57

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

migrated from stackoverflow.com Oct 4 '12 at 14:18

This question came from our site for professional and enthusiast programmers.

63

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
79

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
6

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

Your Answer

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