Forum Discussion

ChrisHanisch's avatar
ChrisHanisch
Copper Contributor
Dec 09, 2022
Solved

Split text in columns

Hi I have a column with texts like this:

1 x 64-907-10  1 x 64-0-28268-01  1 x 64-949-19
1 x 96-51-420-11  1 x 96-52-420-11
1 x 97-50-120-12  1 x 97-57-103-24
1 x A1-11-701-50  1 x A1-18-701-24
1 x 84-51-080-11  1 x 84-59-101-10
1 x C6-36  2 x C6-601 
1 x C6-30  2 x C6-601 

 

This texts I'd like to split into the following:

164-907-10164-0-28268-01164-949-19
196-51-420-11196-52-420-11  
197-50-120-12197-57-103-24  
1A1-11-701-501A1-18-701-24  
184-51-080-11184-59-101-10  
1C6-362C6-601  
1C6-302C6-601  

 

excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Dec 09, 2022

    ChrisHanisch 

    Make sure that there are enough empty columns to the right of the data.

    Select the data, then run the following macro:

    Sub SplitEm()
    Dim rng As Range
    Dim s As String
    Dim c As Long
    Dim a() As String
    Dim i As Long
    Dim b() As String
    Dim j As Long
    Application.ScreenUpdating = False
    For Each rng In Selection.Columns(1).Cells
        c = 0
        s = Replace(rng.Value, Chr(160), " ")
        a = Split(s, Space(2))
        For i = 0 To UBound(a)
            b = Split(a(i), " x ")
            For j = 0 To UBound(b)
                rng.Offset(0, c).Value = b(j)
                c = c + 1
            Next j
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

7 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Dec 11, 2022

    select *,regreplace('( x )|(\s+)','</td><td>',F_A) split from regular_replace_to_split_texts

     

    regular expression

     

     

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 10, 2022

    ChrisHanisch 

    Another possible 365 solution.

    = REDUCE({"Qty","Code"}, column,
     LAMBDA(acc, text,
        VSTACK(
           acc,
           WRAPROWS(TEXTSPLIT(text, {" ", "x"}, , TRUE), 2)
        )
     )
  )

    to give 

     

  • Rodrigo_'s avatar
    Rodrigo_
    Iron Contributor
    Dec 10, 2022

    ChrisHanisch 

    Here's what you need to do:
    1. Highlight all of that cells
    2. Goto Data tab
    3. Click on "Text to Columns"
    4. Choose Delimited and Click "Next >"
    5. On Delimiters, please check only the following:
      - Space
      - Other: (on the text box type: x) refer on the image below:


    6. Click "Next >" and "Finish"

    Tada! That should do the work!

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Dec 09, 2022

        ChrisHanisch 

        Make sure that there are enough empty columns to the right of the data.

        Select the data, then run the following macro:

        Sub SplitEm()
    Dim rng As Range
    Dim s As String
    Dim c As Long
    Dim a() As String
    Dim i As Long
    Dim b() As String
    Dim j As Long
    Application.ScreenUpdating = False
    For Each rng In Selection.Columns(1).Cells
        c = 0
        s = Replace(rng.Value, Chr(160), " ")
        a = Split(s, Space(2))
        For i = 0 To UBound(a)
            b = Split(a(i), " x ")
            For j = 0 To UBound(b)
                rng.Offset(0, c).Value = b(j)
                c = c + 1
            Next j
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

Resources