Take the 2-minute tour ×
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:

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.

4 Answers 4

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

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

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  
-1 This is the same answer as the accepted one –  nixda Sep 7 '13 at 14:13

Your Answer

 
discard

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