Split data from one column without using text to column

Occasional Contributor



I have some data(Invoice Nos) in a col A in a text format separated with"/", i want to split the same with using a formula (NOT TEXT TO COLUMN) coz if new data is added I'll need to go through the operation again.

I tried a combination of LEN, FIND but didn't succeed to get the desired result.


I have this




I want to do this




9 Replies
Use Data, From Table then click the Transform tab on the powerquery editor and click SPlit column. Fill in the details and you're good to go. If your source data changes, simply click refresh all on the data tab.

Hi Deepak,


This can be done by using Power Query also knows as (Get & Transform Data in Excel 2016), and if you don't have Excel 2016, then you have to download and install Power Query Add-in.

Please watch this video to figure out how to do this in Power Query.


Also, you can depend on this formula instead:


Copy it in cell B1 and then drag it to the right.

But it works only if the length of the invoice numbers in column A is always 10.


Hope that helps


As a comment - if it is possible variable number of separators on rows number of columns is to be calculated additionally, something like this

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ToText = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    NumberOfDelimeters = Table.AddColumn(ToText, "Parts", each List.Count(Text.Split([Column1], "/"))-1),
    TotalColumns = List.Max(NumberOfDelimeters[Parts])+1,
    ColumnsList = List.Transform({1..TotalColumns}, each "P"&Text.From(_)),
    SplitColumns = Table.SplitColumn(ToText, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), ColumnsList)


and attached


please go through the attached file. 

Another formula is explained here https://exceljet.net/formula/split-text-with-delimiter

Looks like 

=TRIM(MID(SUBSTITUTE($B4,"/",REPT(" ",LEN($B4))),(L$3-1)*LEN($B4)+1,LEN($B4)))

and attached



lets say your data starts from A2


Pu this formula =TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMNS($A$2:A2)*99-98,99)) in B2 and drag/copy right and and down and it will do the job.

Thank you for this but want to split this data into 4 cells VERTICALLY and not horizontally. Pls see attach for example

then you use this code below.

lets say your data is in Cell A1

Sub Test()
Dim X As Variant
X = Split(Range("A1").Value, Chr(10))
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

@Deepak Sharma 


I have data 


For Column 2, the Text to Column "split" , split the dash from the two elements, but the 2nd split then overides the"cookies shipped column 3"..... I want to delimit and split, but not lose column next to me


Order IDCustomer IDCookies ShippedRevenueCostOrder DateShip DateOrder Status

413383152689 - YT Restaurants9364680234012/31/171/5/18Shipped
751142985245 - Quick Bite Convenience Stores98749352467.512/31/171/4/18Shipped