VBA to trim all letters after a space

[WednesdayC]

Hi All

I apologise for posting twice in one day, but I am under pressure to finish a project.

I am trying to work out how to trim the contents of an InputBox

For instance, assuming I have a variable called FirstName which is read from the string posted to an InputBox and the user enters, say John Doe, instead of John - how can I trim out the Doe and just retrieve John.

Also, if they entered, say, John H Doe, how could I trim out the Doe and just retrieve John H?

Once again, many thanks in advance.

Regards
Wednesday



Regards
Wednesday

 

[Jonmo1]

So would it be fair to say you want to show the text before the LAST space?

Try

Sub test()
Dim MyString As String, x As Long
MyString = "John H Doe"
x = InStrRev(MyString, " ")
If x = 0 Then x = Len(MyString)
MsgBox Trim(Left(MyString, x))
End Sub

 

[WednesdayC]

Hi Jonmo

It works! Just what I wanted. A big thank you.

I realise I made the question rather convoluted!!


You guys rock!

Many thanks

Wednesday

 

[HalfAce]

But... shouldn't that be:
"MyString = "Jon H Mo"

 

[WednesdayC]

Hi HalfAce

Very witty!

Regards
Wednesday

 

[HalfAce]

Hi HalfAce

Very witty!

Regards
Wednesday

Heh heh, I don't get accused of that very often.

 

[Jonmo1]

Heh heh, I don't get accused of that very often.

Maybe if you had a WHOLE Ace...