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]

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

Assuming your TextBox is named TextBox1, then this line of code (not sure where you want to execute it from) will change whatever text is in the TextBox to only the first word (assuming words are separated from each other by spaces)...

TextBox1.Value = Left(TextBox1.Value & " ", InStr(TextBox1.Value, " ") - 1)

 

[NdNoviceHlp]

Maybe...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim a As Variant
a = Split(TextBox1.Text, " ")
TextBox1.Text = a(0)
End Sub

HTH. Dave

 

[sharky12345]

Guys - thank you, will try both.

 

[Rick Rothstein]

Maybe...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim a As Variant
a = Split(TextBox1.Text, " ")
TextBox1.Text = a(0)
End Sub

You do not have to declare a variable to receive the array from the Split function in order to pull an element from it, you can do that directly from the array Split is creating. Also, the space delimiter is the default if omitted from the Split function. Give this, the following should do what your posted code does...

[table="width: 500"]
[tr]
	[td]Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  TextBox1.Text = Split(TextBox1.Text)(0)
End Sub[/td]
[/tr]
[/table]

 

[NdNoviceHlp]

Thanks Rick for the info. Your code produces the same result. Have a nice day. Dave

 

[sharky12345]

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?

 

[mumps]

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

 

[sharky12345]

Perfect - thanks mumps.

 

[mumps]

You are very welcome.