63

My excel column is filled with words like this:

1.) ABC:DCF
2.) DCF:FED

I 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?

Andrea
  • 1,536

5 Answers5

88

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))
nutsch
  • 1,971
68

Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.

Journeyman Geek
  • 133,878
BrOSs
  • 879
26

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 Function

So you'd need to enter:

=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half
Community
  • 1
Jamie Bull
  • 503
9

Paste 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))

Hardi Uutma
  • 91
0

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.

Andy Robertson
  • 251