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!
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!
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
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
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
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.
posted by SuperSquirrel at 4:57 PM on August 18, 2010
=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
posted by SuperSquirrel at 4:58 PM on August 18, 2010
OK, try this. This assumes data length of 6, 4 or "other" only.
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
=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
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
This thread is closed to new comments.
posted by SuperSquirrel at 4:51 PM on August 18, 2010