Apr 17 2018
05:17 AM
- last edited on
Jul 25 2018
11:42 AM
by
TechCommunityAP
Apr 17 2018
05:17 AM
- last edited on
Jul 25 2018
11:42 AM
by
TechCommunityAP
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
Apr 17 2018 05:21 AM
Apr 17 2018 08:18 AM
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
Apr 17 2018 09:38 AM
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
Apr 17 2018 10:02 AM
hi
please go through the attached file.
Apr 17 2018 10:47 AM
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
Apr 17 2018 02:46 PM - edited Apr 17 2018 02:46 PM
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.
Apr 19 2018 08:37 AM
Thank you for this but want to split this data into 4 cells VERTICALLY and not horizontally. Pls see attach for example
Apr 20 2018 06:48 AM
Oct 22 2022 06:52 PM
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 |