how to break a string in to substring in excel?

Tag: excel Author: fmx0910 Date: 2009-08-21

original column is like:


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.

This probably belongs on - Excel formulas are a bit borderline as far as programming
@therefromhere: I'm going to disagree here. Excel (indeed any spread sheet) is Turing complete. They are a little clunky for serious programming, but they are every bit as powerful as <yourFavoriteLanguage>. Without knowing zh_'s use case it is not fair to more the question.
@dmckee - Is excel still Turing complete without its macro language (VBA)? Does a proof exists that "any spreadsheet" is Turing complete?

Best Answer

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


Good call, but you might have to replace "::" with a single character in order to split this data out to columns.
Nice, I wasn't aware of that functionality.
Quick easy way I didn't know about, Thanks!
Please post the relevant excerpt from the source or add code to your answer.

Other Answer1

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.


What if you have no delimiter, but a set number of chars you need to split out? I'm looking for the excel equivalent of the String.Substring method
You just use the MID function. MID(text, start_num, num_chars In Excel start_num = Index + 1, and you must include a num_chars although this could be 99999 and it would return until the end of the string.

Other Answer2

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.

Other Answer3

If you would like a simple function, you can use the following VBA code.

Function SplitTextToNum(rngInput As Range, sepString As String)
    Dim CallerRows As Long, CallerCols As Long, DimLimit As Long
    Dim outvar As Variant
    outvar = Split(rngInput.Value, sepString, -1, vbBinaryCompare)
    If Application.Caller.Rows.Count > 1 Then
        SplitTextToNum = Application.Transpose(outvar)
        SplitTextToNum = outvar
    End If
End Function

You can use Ctrl+Shift+Enter over a range of cells after entering the formula referring to the cell in which you have the string that you need to be split up.