How to split a string based on “:” in MS-Excel?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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