Adding string to parts of a string in Excel

Question

In column A, I have a list of random words, separated by "; "(semicolon & space), and randomly having 1 to 3 words in each cell.

For example, A1 - A5 would contain the following:

apple
banana; carrot
durian; eggplant
fig
grape; honeydew; icecream

I am trying to surround each word with a specified string. For example, "I eat " before the word,
" everyday.", after the word, which should look like the following in column B.

I eat apple everday.
I eat banana everday.;I eat carrot everday.
I eat durian everday.; I eat eggplant everday.
I eat fig everday.
I eat grape everday.; I eat honeydew everday.; I eat icecream everday.

If each cell contains only one word, it would be a simple process of just concatenating:

=CONCATENATE("I eat ",A1," everyday.")

But then when the number of words are random, it starts to get confusing. Of course there is a solution by separating by the semicolons into different columns, add the new string, and add everything together, but I was going for doing it in a single cell.

Xeper Caz · Accepted Answer · 2015-09-02 00:01:25Z

1

If the delimiter is always a semicolon, and the cell's data never ends in a semicolon, a simple way would be to use Excel's Substitute function inline with your concatenation.

="I eat "&SUBSTITUTE([CellNumber], ";", " every day. I eat ")&" every day."

answered Sep 2, 2015 at 0:01

Xeper Caz

487 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

musasi Over a year ago

Hi Xeper Caz. Its perfect!

Collectives™ on Stack Overflow

Adding string to parts of a string in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related