CODEWITHSUNDEEP
excel-formulaexcel-2010
sam
sam

Reputation: 955

Split the data in a column excel

I have a column that contains data for eg: 6B31-21045M22-AA
I'm trying to split the data before and after '-'. Like 

A                  B     C         D
6B31-21045M22-AA   6B31  21045M22  AA

I tried 

 =LEFT(A2, SEARCH(“-”,A2)-1)  and =Right(A2, SEARCH(“-”,A2)-1)

but if "-" occurs more than once then how do i split the 6B31-21045M22-AA or 6B31-21045M22-AA-SWQ

Upvotes: 0

Views: 78

Answers (2)

Jiří Pešík
Jiří Pešík

Reputation: 344

You don't need a formula or a VBA script. You can use Text to Columns function. Simply select the column with your data and use Text to Columns button on the Data panel. Then in a Wizard select Delimiter and set a "-" symbol as a delimiter.

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96791

With data in A1, in B1 enter:

=TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across:

enter image description here

(this can also be done with VBA)

EDIT#1:

Using VBA, select the cell you wish to process and run this:

Sub Splitter()
    Dim ary, i As Long, a
    With ActiveCell
        ary = Split(.Value, "-")
        i = 1
        For Each a In ary
            .Offset(0, i).Value = a
            i = i + 1
        Next a
    End With
End Sub

Upvotes: 0

Related Questions