SOLVED

Split text in columns

Copper Contributor

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  

 

7 Replies

@ChrisHanisch 

If you have access to TEXTSPLIT, you could use:

 

=TEXTSPLIT(A1,{" x ","  "})

 

@Patrick2788 No I do not have TEXTSPLIT
best response confirmed by ChrisHanisch (Copper Contributor)
Solution

@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

@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:

ssss.PNG
6. Click "Next >" and "Finish"

Tada! That should do the work!

@ChrisHanisch 

Another possible 365 solution.

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

to give 

image.png

 

... or miss out the WRAPROWS to get ...

image.png

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

 

regular expression

 

 

Screenshot_2022-12-11-20-45-14-730_cn.uujian.browser.jpg

 

1 best response

Accepted Solutions
best response confirmed by ChrisHanisch (Copper Contributor)
Solution

@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

View solution in original post