CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
musasi
musasi

Reputation: 15

Adding string to parts of a string in Excel

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.

Upvotes: 1

Views: 95

Answers (1)

Xeper Caz
Xeper Caz

Reputation: 48

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."

Upvotes: 1

Related Questions