Excel delimiters in one column

August 18, 2010 4:46 PM

Excel filter: How to insert delimiters within one column??

I have a column of data that contains several codes in a row. For example, if the codes are 11, 13, and 09, the data in the cell looks like: 111309. Is there an easy way to insert delimiters (commas) so that the data in the cell looks like 11,13,09? Or any other fast solutions to separate the codes? Thanks!

posted by shotgunbooty to Computers & Internet (7 answers total)

Probably, but it depends on your source data. Is it always 3 codes of 2 digits each, or can there be variations, like a code that is 3 digits, or a cell with only 2 codes?
posted by SuperSquirrel at 4:51 PM on August 18, 2010

If all the codes are two characters long, you can use the function mid() to grab two character strings. You can then use the function concatenate to join together the extracted strings and the commas.

If the data is in cell A1, a formula like this in B1 will work:
=concatenate( mid( a1, 1, 2 ), ",", mid( a1, 3, 2 ), , ",", mid( a1, 5, 2) )
posted by orthogonality at 4:55 PM on August 18, 2010

Response by poster: SuperSquirrel - so the codes are always 2 digits, but sometimes there are only 2 codes and sometimes only 1 2-digit code.
posted by shotgunbooty at 4:56 PM on August 18, 2010

Here is a formula that will work if the data is always 6 digits (3 codes of 2 digits). It assumes source data is in cell A1. You would put this formula into cell B1, for example. If you need the results without the underlying formula, copy the cells, and paste them into a new column using the "Paste Special -> Values Only" option. Then delete your original column and the calculated one.

=CONCATENATE(LEFT(A1,2), ",",MID(A1,3,2),",",MID(A1,5,2))

posted by SuperSquirrel at 4:57 PM on August 18, 2010

Aw crap, posted without preview. Both orthogonality and my solutions will only work with 6 digits. If the source data length might be different, you need some logic. If you give me a few minutes, I can work something out for you.
posted by SuperSquirrel at 4:58 PM on August 18, 2010

OK, try this. This assumes data length of 6, 4 or "other" only.


=IF(LEN(A1)=6,CONCATENATE(LEFT(A1,2), ", ",MID(A1,4,2),", ",MID(A1,5,2)),IF(LEN(A1)=2,CONCATENATE(LEFT(A1,2), ", ",MID(A1,3,2)),A1))

Note that there are no line breaks in the actual formula.

Please don't use this for any mission-critical funtionality until you've thoroughly tested it.
posted by SuperSquirrel at 5:08 PM on August 18, 2010

Is this spreadsheet where the data is all already filled in and won't be changing anymore?

In that case, you can just highlight the column with the codes, select the "Text to Columns" command, set your data type as "Fixed width", and then set the field widths to to appropriate two spots in the column. This will then split the column of data into three columns of two digits each (and leave blanks for columns with less than that).
posted by kosmonaut at 7:42 PM on August 18, 2010 [2 favorites]