Dec 08 2022 11:31 PM - edited Dec 08 2022 11:33 PM
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 |
Dec 09 2022 09:02 AM - edited Dec 09 2022 12:51 PM
Dec 09 2022 12:38 PM
SolutionMake 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
Dec 09 2022 10:50 PM
@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!
Dec 10 2022 02:41 AM
Another possible 365 solution.
= REDUCE({"Qty","Code"}, column,
LAMBDA(acc, text,
VSTACK(
acc,
WRAPROWS(TEXTSPLIT(text, {" ", "x"}, , TRUE), 2)
)
)
)
to give
Dec 11 2022 04:48 AM - edited Dec 11 2022 04:55 AM
select *,regreplace('( x )|(\s+)','</td><td>',F_A) split from regular_replace_to_split_texts
regular expression
Dec 09 2022 12:38 PM
SolutionMake 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