59

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?

| improve this question | |
65

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

| improve this answer | |
85

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))
| improve this answer | |
  • 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
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
| improve this answer | |
  • 1
    Very Nice, didnt know it was so easy to create your own formulas – cowls May 13 '14 at 11:13
  • 1
    This is perfect for splitting a URL into its component parts. – Underverse Jun 2 '15 at 1:42
8

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

| improve this answer | |
  • 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 5 '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 7 '16 at 8: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 6: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
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy