Text to columns not function not worked for a cell

[hunghung]

I have a cell with data combined together with semicolon ";" as cell A1. However, when I use text to columns function with delimited semicolon, it only appear two columns B1 and C1 with results as below.
Please help me this case.
Thanks,

Book1
	A	B	C
1	12600215;Delivery Date Hemp Blend T-shirt M;2024-11-08 113 Chalk White;S;30;7323451002397;	12600215	Delivery Date
Sheet1

 

[RoryA]

What are you expecting to happen? Text to columns will not split to new rows, but you could use an additional new line delimiter to split the other data to new columns on the same row.

 

[hunghung]

I want result as below from cell B1 to G1. But I tried text to columns but it only have result with two columns as above.

Book1
	A	B	C	D	E	F	G
1	12600215;Delivery Date Hemp Blend T-shirt M;2024-11-08 113 Chalk White;S;30;7323451002397;	12600215	Delivery DateHemp Blend T-shirt M	2024-11-08113 Chalk White	S	30	7323451002397
Sheet1

 

[RoryA]

I think you will have to get rid of the line feed characters in the cells before that will work. If you are using 365 you could just use the Textsplit function instead.

 

[Alex Blakenburg]

I want result as below from cell B1 to G1. But I tried text to columns but it only have result with two columns as above.

Are you sure about that ? If I was doing it I would want to split on both the semi-column AND the line break character.
As Rory mentioned if you are using 365 it would look like this:

Book4
	A	B	C	D	E	F	G	H	I	J
1	12600215;Delivery Date Hemp Blend T-shirt M;2024-11-08 113 Chalk White;S;30;7323451002397;	12600215	Delivery Date	Hemp Blend T-shirt M	2024-11-08	113 Chalk White	S	30	7323451002397
Sheet1 (2)

Cell Formulas
Range	Formula
B1:J1	B1	=TEXTSPLIT(A1,{";"," "})
Dynamic array formulas.

To achieve the same using Text to Columns requires quite a few steps.
• Select column A
• ctrl+H (replace)
○ In find what put ctrl+J
○ In replace with put ; ie semi-column
○ Replace All
• Data / Text to columns
○ With column A still selected
○ Delimited > Other ; (semi-colon)
○ Next
○ In the next screen select the date column Select date and the format ymd
Now you can either select the last number column and Select Text or format as a number after you complete the process depending on whether its meant to be a number or more like a serial number.
○ Finish
If you didn't make the last column Text it will mostly likely show as scientific notation so just format it as a number with no decimal places.

 

[Fluff]

No need to replace the linefeed with a semi-colon. You can simply select semicolon as one delimiter & then select other & type Ctrl J in the box next to it.

 

[hunghung]

Many thanks for Fluff, Rory and Alex for clarifying me. The solution is I should remove linefeed from cell A1 before doing text to columns or select semicolon as one delimiter & then select other & type Ctrl J in the box next to it on text to columns function.

 

[hunghung]

I have one more question. I have cell A1 and A2 as below with data separated by multiple spaces. Is there any way to use Text to columns to have data from cells B1 to G1 and B2 to G2
Thank you in advance.

Book2
	A	B	C	D	E	F	G
1	14300012 Abisko Sun-hoodie Dress W 537 Ultramarine XXS 10 7323451086441	14300012	Abisko Sun-hoodie Dress W	537 Ultramarine	XXS	10	7323451086441
2	12600265 Hemp Blend Out Here T-shirt M 160 Ochre S 4 7323451084256	12600265	Hemp Blend Out Here T-shirt M	160 Ochre	S	4	7323451084256
Sheet1

 

[Fuji]

Here's a vba solution, if you like.

Sub test()
    Dim a, i&, ii&, s$, x
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "(;|\n+| {2,})"
            For i = 1 To UBound(a, 1)
                s = a(i, 1)
                If s <> "" Then
                    x = Application.Trim(Split(.Replace(a(i, 1), Chr(2)), Chr(2)))
                    If UBound(x) > UBound(a, 2) Then
                        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(x))
                    End If
                    For ii = 1 To UBound(x)
                        a(i, ii) = x(ii)
                    Next
                End If
            Next
        End With
        .Columns(2).Resize(, UBound(a, 2)) = a
    End With
End Sub

Book1
	A	B	C	D	E	F	G	H	I
1	12600215;Delivery Date Hemp Blend T-shirt M;2024-11-08 113 Chalk White;S;30;7323451002397;	12600215	Delivery Date	Hemp Blend T-shirt M	2024/11/8	113 Chalk White	S	30	7323451002397
2	14300012 Abisko Sun-hoodie Dress W 537 Ultramarine XXS 10 7323451086441	14300012	Abisko Sun-hoodie Dress W	537 Ultramarine	XXS	10	7323451086441
Sheet1

 

[hunghung]

Here's a vba solution, if you like.

Sub test()
    Dim a, i&, ii&, s$, x
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 2).Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "(;|\n+| {2,})"
            For i = 1 To UBound(a, 1)
                s = a(i, 1)
                If s <> "" Then
                    x = Application.Trim(Split(.Replace(a(i, 1), Chr(2)), Chr(2)))
                    If UBound(x) > UBound(a, 2) Then
                        ReDim Preserve a(1 To UBound(a, 1), 1 To UBound(x))
                    End If
                    For ii = 1 To UBound(x)
                        a(i, ii) = x(ii)
                    Next
                End If
            Next
        End With
        .Columns(2).Resize(, UBound(a, 2)) = a
    End With
End Sub

Book1
	A	B	C	D	E	F	G	H	I
1	12600215;Delivery Date Hemp Blend T-shirt M;2024-11-08 113 Chalk White;S;30;7323451002397;	12600215	Delivery Date	Hemp Blend T-shirt M	2024/11/8	113 Chalk White	S	30	7323451002397
2	14300012 Abisko Sun-hoodie Dress W 537 Ultramarine XXS 10 7323451086441	14300012	Abisko Sun-hoodie Dress W	537 Ultramarine	XXS	10	7323451086441
Sheet1

I'm not familiar with RegExp, do you have another solution for case with multiple space below.
Thanks,

Book3
	A
1	14300012 Abisko Sun-hoodie Dress W 537 Ultramarine XXS 10 7323451086441
Sheet1