CODEWITHSUNDEEP
excel
zh_
zh_

Reputation:

how to break a string in to substring in excel?

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.

Upvotes: 1

Views: 28679

Answers (5)

André Hoffmann
André Hoffmann

Reputation: 3553

This page explains how to do just that using the "Text to Columns" function.

Copied for your convenience:

Highlight all of your cells with the data.

Select The Topmost Cell In The Column, E.G. A1 Hold CTRL+SHIFT And Then Press The Down Arrow. OK, Once We've Done That, Go To "Data" Menu And Select "Text To Columns". On The Text To Columns Window, Select "Delimited" And Then Hit "Next". In The Following Window, Choose "Other" For Type Of Delimiter And Use The Minus/Hyphen Sign - Hit Finish.

Now you will have two columns, from your example, the first column will contain data like "Animals" and the other column will contain the data " House of The Rising Sun". (note the SPACE in front of "House")

To get rid of that SPACE we're going to use the TRIM function.

In cell C1 (or the column to the right of the song titles) type in this formula.

=TRIM(B1)

Then double-click on that little black box on the excel cursor to copy the formula down the whole range. Any spaces at the Start or end of the text string will be removed.

Upvotes: 3

Ken Brown
Ken Brown

Reputation: 11

Here is a very simple way to extract the 5th character from the left from a text string in Excel:

Suppose the character string ABCDEFGHIJ is stored in cell A1 in an Excel Spreadsheet, then the following formula

=RIGHT(LEFT(A1,5),1)

produces the 5th character from the left in the string, namely “E”.

Upvotes: 1

d0m1n0
d0m1n0

Reputation: 181

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

Upvotes: 0

JDunkerley
JDunkerley

Reputation: 12505

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.

Upvotes: 2

carl
carl

Reputation: 50554

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.

Upvotes: 1

Related Questions