SOLVED

New Contributor

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

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

7 Replies

# Re: Split text in columns

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

# Re: Split text in columns

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

# Re: Split text in columns

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``````

# Re: Split text in columns

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

# Re: Split text in columns

Another possible 365 solution.

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

to give

# Re: Split text in columns

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

# Re: Split text in columns

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

regular expression