Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

My excel column is filled with words like this:


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 Oct 4 '12 at 14:18

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

3 Answers 3

up vote 34 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


in C1

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.