Get first word from string from a textbox

[excel_2009]

Hi excel gurus

I'm currently working on a user form which works great however a user has to input similar data in two boxes but in reality the first box contains all the data however the second box only requires the first word from the initial text box e.g

First text box contains :

Samsung Galaxy S2

The second text box only needs the following:

Samsung

Is there anyway of doing this efficiently as opposed to keeping two text boxes? I.e. storing the first word so that I can use it later in my code? Thus removing the need for the second box?


I hope this makes sense!

Thank you

 

[JLGWhiz]

Assume text box name is tb1 and you use the Exit event. Establish a public variable at the top of code module 1:
Public MyVar As String

Private Sub tb1_Exit()
 myVar = Left(Me.tb1.Value, InStr(Me.tb1.Value, " ") - 1)
End Sub

myVar would store the current value of tb1.

 

[frank_AL]

Give this code a try. Pay attention to the Comments added after a couple of lines as you will need to make adjustments based on your Variable names.
I just recently discovered the VBA Split Function and it is a versatile tool for splitting apart a text string. Take some time and Google it to learn more about all the ways you can use this function.

Sub FindFirstWord()


Dim WrdArray() As String
    
WrdArray = Split(CurrVal, " ")  '  Change CurrVal to be the Variable from the first text box.
ActiveCell.Value = WrdArray(0)  '  Change "ActiveCell.Value" to be the Variable you want to use.  I assume it would be the Variable you currently have assigned to your second text box.


End Sub

 

[mikerickson]

Rather than messing with a particular storage location (text box or variable), I might go with a genetic UDF to be applied whenever needed.

Function FirstWordOf(ByVal aSentence As String, Optional PunctuationMarks as String = ".,?/-") As String
    Dim i as Long
    For i = 1 to Len(Punctuation)
        aSentence = Replace(aSentence, Mid(Punctuation, i ,1), " ")
    Next i

    FirstWordOf = Split(aSentence & " ", " ")(0)
End Function