Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

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

up vote 44 down vote accepted

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

share|improve this answer

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

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

You must log in to answer this question.