VBA Text to column first space only

[Joey_Ng]

Hi,

hoping someone could help me with the vba code for text to column but split using the first space only. Eg, John Sam Smith, I want to split using only the first space after John, ignoring the second space (and other spaces for extra text), eg result: "John" and "Sam Smith"
below code split using all spaces:
Selection.TextToColumns DataType:=xlDelimited, Space:=True

Thanks,

Joey

 

[alansidman]

Can be achieved with Power Query in one step. Post back with sample data if interested in this type of solution.

 

[BSALV]

Sub test()
     Selection.Name = "Bart" 'named range
     Selection.Offset(, 1).Value = [LEFT(bart&" ",SEARCH(" ",bart&" ")-1)]
End Sub

 

[Peter_SSs]

Try this with a copy of your data.

Sub SplitFirstSpace()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate("substitute(" & .Address & ","" "","";"",1)")
    .TextToColumns DataType:=xlDelimited, Semicolon:=True, Space:=False, Other:=False
  End With
End Sub

Before

Joey_Ng.xlsm
	A	B
1	Name Last Name
2	John Sam Smith
3	Ted Jones
4
5	Bob Bill Joe Hill
6	Sam
7	Ken Tom Hall
Sheet1

After

Joey_Ng.xlsm
	A	B
1	Name	Last Name
2	John	Sam Smith
3	Ted	Jones
4
5	Bob	Bill Joe Hill
6	Sam
7	Ken	Tom Hall
Sheet1

 

[kevin9999]

Another option, because it's nice to have choices This one keeps the first name if that's all there is.

Sub SplitFirstSpaceOnly()
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Range(Cells(i, 1), Cells(i, 2)).Value = Split(Cells(i, 1).Value, " ", 2)
    Next i
End Sub

Before

personal.xlsb
	A	B
1	John Sam Smith
2	John Sam Smith
3	John Smith
4
5	John Sam Smith
6	John
7	John Sam Smith
Sheet1

After

personal.xlsb
	A	B
1	John	Sam Smith
2	John	Sam Smith
3	John	Smith
4
5	John	Sam Smith
6	John	John
7	John	Sam Smith
Sheet1

 

[Peter_SSs]

This one keeps the first name if that's all there is.

I thought that is what mine did. To me, yours actually introduces a new second name.

Consider the result of your code after starting with these two different rows of data.

Joey_Ng.xlsm
	A
1	Anthony Anthony
2	Anthony
Sheet2

 

[kevin9999]

I thought that is what mine did. To me, yours actually introduces a new second name.

Consider the result of your code after starting with these two different rows of data.

Joey_Ng.xlsm
	A
1	Anthony Anthony
2	Anthony
Sheet2

Point taken Peter. Perhaps this is closer to what the OP wants?

Sub SplitFirstSpaceOnly()
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If InStr(1, Trim(Cells(i, 1)), " ") > 0 Then
            Range(Cells(i, 1), Cells(i, 2)).Value = Split(Cells(i, 1).Value, " ", 2)
        End If
    Next i
End Sub

Before

personal.xlsb
	A	B
1	Anthony Anthony
2	Anthony
3	John Smith
4
5	John Sam Smith
6	John
7	John Sam Smith
Sheet1

After

personal.xlsb
	A	B
1	Anthony	Anthony
2	Anthony
3	John	Smith
4
5	John	Sam Smith
6	John
7	John	Sam Smith
Sheet1

 

[Peter_SSs]

Perhaps this is closer to what the OP wants?

I agree in relation to the results, but I liked the idea of doing them all at once better. ?

 

[Peter_SSs]

.. another way without processing row-by-row

Sub SplitFirstSpace_v2()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("replace(#,1,find("" "",#&"" ""),"""")", "#", .Address))
    .Value = Evaluate(Replace("left(#,find("" "",#&"" "")-1)", "#", .Address))
  End With

 

[alansidman]

An alternative using the data in Peter's post and applying the following Mcode in Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name Last Name] <> null)),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Name Last Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Name Last Name.1", "Name Last Name.2"})
in
    #"Split Column by Delimiter"