My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FEDI want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC" in cell "B:1" and "DCF" in cell "C:1" and also corresponding values in each column. How to do this?
5 Answers
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
Text to columns will work. Another option, if you want to keep the original value, is to use formulas:
in B1
=left(a1,find(":",a1)-1) in C1
=mid(a1,find(":",a1)+1,len(a1))If you can use VBA then you can make use of the Split() function. Here's a User-Defined Function (UDF) that you can use in a cell. It splits on your choice of character and returns the nth element of the split list.
See How do I add VBA in MS Office?for information on how to define a UDF.
Function STR_SPLIT(str, sep, n) As String Dim V() As String V = Split(str, sep) STR_SPLIT = V(n - 1)
End FunctionSo you'd need to enter:
=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second halfPaste it to B1 and fill it to columns on right and rows down:
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
Edit: I previously posted localized version of the formula, where ',' was replaced with ';'. That doesn't work in US-version of Excel:
=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))
You can also use an array formula to do this.
If you have ABC:DEF:GHI in cell A1 then if you enter the following formula in B1:D1 it will split out the text in A1 by the colon character.
=IFERROR(MID(":"&$A1&":",SMALL(IF(MID(":"&$A1&":",ROW($1:$999),1)=":",ROW($1:$999),""),COLUMN()-1)+1,SMALL(IF(MID(":"&$A1&":",ROW($1:$999),1)=":",ROW($1:$999),""),(COLUMN()))-SMALL(IF(MID(":"&$A1&":",ROW($1:$999),1)=":",ROW($1:$999),""),COLUMN()-1)-1),"")
You need to hit CTRL+ENTER to enter the formula. The main part that is repeated 3 times is this bit... IF(MID(":"&$A1&":",ROW($1:$999),1)=":",ROW($1:$999),"") which gets an array of the positions of the colon (using the rows 1 to 999 although you could use more). This returns the array
1;"";"";"";5;"";"";"";9;"";"";"";13;"";"";""
Using the SMALL function you get the 1st and second smallest values which gives you 1 and 5. Then it uses the MID function to get the string between the first and 5th characters (bearing in mind the string is ":ABC:DEF:FHI:" because we added the separator to the beginning and end). Then in the next column you get the text between the 5th and 9th characters etc.
