How do I extract a word from a string?

[Gabe Tiger]

EG.

I want to only get the first name of the following full names that are
given in column A:

John M. Ford
Albert Cushy
Bertland Sunderland
Hugo Sanchez d'Angelo


My idea is to find how many spaces there is until the first " "
character and then use the mid function until this character (" ") to
create a new string with only the first name in it.

Anyone know how create a synthax like this?

Gabe.

 

[Guest]

FirstName = Left(FullName, InStr(FullName, " ") - 1)

 

[Guest]

Charlie's code is great...in this instance.

I found at times that VBA has problems with referencing 'a space' using
double quotes. Try creating the following and you'll get an error:

range("a1").formula = "=if(activesheet.name = "Sheet1",True, " ")"

By trying to input or search for a space using " " within a quoted line, VBA
crashes. For conistency I use the reference to Char(32), the ASCII reference
to a space.

Charlie's example would now look like the following:

FirstName = Left(FullName, InStr(FullName, Char(32)) - 1)

 

[Andrew Taylor]

The error comes from the fact that the formula has nested
quotes. It should be written as
Range("a1").Formula = _
"=if(activesheet.name = ""Sheet1"",True, "" "")"

(line split to avoid wrapping)

I've never known VBA to have any problems with spaces, and
for reasons of readability I'd strongly recommend against
using Chr(32) (not Char(32) BTW)

 

[Guest]

Problems with " " (literal space)? Me neither, but I do often come across
programmers who haven't yet discovered the "double-up" requirement. It
applies to apostrophes also, in the event one wants to reference a sheet name
that contains an apostrophe:

Sheet.Name = "Don't"

Range("'Don''t'!A1")="""Ok, I Won't"""