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?

Improve this question

5 Answers 5

Reset to default
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
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))
Improve this answer
2
  • 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, 2013 at 14:43
  • This is a brilliant solution
    – jsg
    Jun 28, 2017 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
2
  • 1
    Very Nice, didnt know it was so easy to create your own formulas
    – cowls
    May 13, 2014 at 11:13
  • 1
    This is perfect for splitting a URL into its component parts.
    – Underverse
    Jun 2, 2015 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
6
  • 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, 2016 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, 2016 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, 2016 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, 2016 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, 2016 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