Forum Discussion

Deepak Sharma's avatar
Deepak Sharma
Copper Contributor
Apr 17, 2018

Split data from one column without using text to column

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

700047820370005254927000532700700053675670005623657000571920

 

Thanks

Formulas & Functions
tips and tricks

9 Replies

Sort By
  • jhonathonbadalof's avatar
    jhonathonbadalof
    Copper Contributor
    Oct 23, 2022

    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
  • KRV 182's avatar
    KRV 182
    Copper Contributor
    Apr 19, 2018

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

    • Jamil's avatar
      Jamil
      Bronze Contributor
      Apr 20, 2018
      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
  • Jamil's avatar
    Jamil
    Bronze Contributor
    Apr 17, 2018

    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.

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 17, 2018

    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 https://www.microsoft.com/en-us/download/details.aspx?id=39379.

    Please watch this https://www.youtube.com/watch?v=0roS4OVfD9k 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

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 17, 2018
    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.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 17, 2018

      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

Resources