Remove all but first word in Textbox

[sharky12345]

Can someone show me how to remove all words, characters and extra spaces from a Userform textbox with the exception of the first word?

 

[Rick Rothstein]

Guys, can I ask another question please?

I'm now trying to achieve the same on a worksheet and need it to apply to all cells between C2:C30. However, some of those cells will only contain 1 word so is there a way I can achieve it without it throwing an error if there is only 1 word anyway, i.e. get it to ignore cells with only 1 word?

Which code that has been posted for TextBox above do you think will error if the TextBox (or one of your cells) only had one word in it?

 

[Rick Rothstein]

Try:

Sub Test()
    Dim rng As Range
    For Each rng In Range("C2:C30")
        If InStr(1, rng, " ") > 0 Then
            rng = Split(rng)(0)
        End If
    Next rng
End Sub

The only time Split would have a problem is if the value it is splitting contains no text... Split will work fine if the value only has one word. If you want to protect against an empty value, simply concatenate a blank space onto the variable so it has a blank space to find...

Sub Test()
  Dim Rng As Range
  For Each Rng In Range("C2:C30")
    Rng = Split(Rng & " ")(0)
  Next
End Sub

Of course, since the range is fixed in size, we can also do this more compactly with just a single line of code...

Sub Test2()
  [C2:C30] = [IF({1},LEFT(C2:C30,FIND(" ",C2:C30&" ")-1))]
End Sub