Split data from one column without using text to column

[Deepak Sharma]

Hi,

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

7000478203/7000525492/7000532700/7000536756/7000562365/7000571920

I want to do this

7000478203

7000525492

7000532700

7000536756

7000562365

7000571920

Thanks

[#2]

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.

[#3]

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:

=MID($A1,IF(COLUMNS($A$1:A1)>1,(COLUMNS($A$1:A1)-1&COLUMNS($A$1:A1)-1)+1,COLUMNS($A$1:A1)),10)

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

Haytham

[#4]

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

let
    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)

in
    SplitColumns

and attached

[#5]

hi

please go through the attached file. 

[#6]

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

[#7]

Deepak,

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.

[#8]

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

[#9]

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

[#10]

@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

413383	152689 - YT Restaurants	936	4680	2340	12/31/17	1/5/18	Shipped
751142	985245 - Quick Bite Convenience Stores	987	4935	2467.5	12/31/17	1/4/18	Shipped