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

 

[BSALV]

Evaluate(Replace("replace(#,1,find("" "",#&"" ""),"""")", "#", .Address))

@Peter_SSs , i'm surprised by this construction.
It works, but how does it work ?
Do you have a link or documentation about that "#"

 

[Peter_SSs]

Do you have a link or documentation about that "#"

The # is simply a placeholder for the range address, and could be any symbol that would not already be in the 'formula part'.

Instead of
Evaluate(Replace("replace(#,1,find("" "",#&"" ""),"""")", "#", .Address))
it could have been written without the # as
Evaluate("replace(" & .Address & ",1,find("" ""," & .Address & "&"" ""),"""")")
but the former has two advantages for me

1. The bold 'formula part' reads much more like a worksheet formula to me, making the code easier to understand/debug, and
2. Use of the placeholder makes the code shorter - not a lot here with only two instances of it, but if there are 3, 4, 5 etc then the compacting can be significant.

 

[BSALV]

wow, as i see it now, less spectacular, but a very nice trick !
thanks

 

[Joey_Ng]

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

Works great. Thank You!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.