forumla to separate values with semicolon

**kuder mcgavin** · 10-15-2012, 11:57 AM

I have a list of values in column A and I need to get them in a list of values, separated by a semicolon. For example: Column A has dog in A1, cat in A2, and bird in A3. I'd like to get the following in B1: dog;cat;bird

Thanks

**twiggywales** · 10-15-2012, 12:05 PM

you could use text to columns and then select ; as your deliminator

or if you need a formula

put this in B1 then drag

Please Login or Register  to view this content.

**twiggywales** · 10-15-2012, 12:09 PM

oh sorry i just realised you were putting it together not splitting it

use the & symbol to put the word together

like this

Please Login or Register  to view this content.

etc

**Moo the Dog** · 10-15-2012, 12:13 PM

if you have a long list (A1:Axxx), you can try this:

in cell b1, enter: =A1
in cell b2, enter: =IF(ISBLANK(A2),"",B1&";"&A2)

fill that formula down for as many rows as you have. The last row with a result would be the string formatted like you want.

I am thinking a VBA loop would handle this much more simply. If only I knew VBA.

**tigeravatar** · 10-15-2012, 12:26 PM

kuder mcgavin,

Just in case you're ok with a UDF, you can use the ConcatAll UDF I posted here
http://www.excelforum.com/tips-and-t...geravatar.html

Then in B1, you could use this formula:

Please Login or Register  to view this content.

**Moo the Dog** · 10-15-2012, 12:38 PM

Here's some VBA I found that seems to work (Insert a new module and paste this code):

Please Login or Register  to view this content.

So if your list is in A1:Axxx, then in cell B1 you can enter: =ConcRange(A1:Axxx)

Of course, you can set the 'xxx' part of the range to a row that your list would never go down to (100, 1000, whatever...)

- Vince