how to break a string in to substring in excel?

Question

original column is like:

0.45::rafas::4.0::0.0::0.9
0.35::rasaf::4.0::110.0::1.0

and i would like to break the string in to the following (:: as separator) in Excel

                             col1   col2   col3   col4   col5
0.45::rafas::4.0::0.0::0.9   0.45   rafas   4.0   0.0    0.9
0.35::rasaf::4.0::110.0::1.0 0.35   rasaf   4.0   110    1.0

Please help.

Answer 1

Here it is explained how to do that using the "Text to Columns" function.

Answer 2

Excel (and OpenOffice) have a functionality to split Text into Columns. Highlight all the columns that conform to this schema, then go to the Data menu, and select "Text to Columns". Used a delimited separator and specify it as ":" while treating consecutive delimitors as one.

Answer 3

If you wanted to do it with forumlae rather than the "text to columns" functions you could use:

Assuming string in A1

in B1: =FIND("::",$A1)
in C1: =FIND("::",$A1,B1+1)

Then copy C1 over D1:E1

in F1: =MID($A1,1,B1-1)
in G1: =MID($A1,B1+2,C1-B1-2)

Then copy G1 over H1:I1 And finally

in J1: =MID($A1,E1+2,LEN($A1)-E1-1)

The results of the split will be in F1:J1. You can always hide columns B:E as they are just internal to the splitting. This can then be done on as many rows as you need and if the value in A1 is update all other values will be changed. However, it is on a fixed number of columns but can easily be expanded if needed.