Take the 2-minute tour ×

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

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

add comment

4 Answers

up vote 13 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
add comment

You can also use Data -> Text to Columns -> and specify your text delimiters. http://office.microsoft.com/en-001/excel-help/split-text-into-different-cells-HA102809804.aspx

share|improve this answer
-1 This is the same answer as the accepted one –  nixda Sep 7 '13 at 14:13
add comment

If you can use VBA then you can make use of the Split() function. Here's a UDF you can use in a cell. It splits on your choice of character and returns the nth element of the split list.

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 at 11:13
add comment

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
add comment

Your Answer

