61

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,516
  • 4
  • 17
  • 19

5 Answers5

87

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,963
  • 12
  • 14
  • 2
    The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not). – psychowood Jul 16 '13 at 14:43
  • This is a brilliant solution – jsg Jun 28 '17 at 10:09
65

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
  • 127,463
  • 52
  • 260
  • 430
BrOSs
  • 851
  • 7
  • 5
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
  • 1
  • 6
  • 16
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
  • 1
  • 2
  • 1
    Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks. – fixer1234 Sep 05 '16 at 18:02
  • Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell. – Hardi Uutma Sep 07 '16 at 08:11
  • Excel says that this is not a valid formula when you paste it into a cell. Please check and update. – thilina R Sep 23 '16 at 14:01
  • Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear. – Hardi Uutma Sep 25 '16 at 06:39
  • Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function. – CWilson Dec 15 '16 at 18:01
  • Great! Very elegant solution - unlimited columns, only limitation is the size per String of 998. – Paschi Jan 17 '17 at 15:32
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
  • 121
  • 1
  • 5