Extracting First Name from cell with first name and last name

[Matt]

I have searched several posts and am still stumped. I have a first
name and last name in Cell B10 on Sheet1. I want to extract the first
name with no spaces. It is part of a bigger code where I am opening
outlook and making an email. The end result is to use the person's
first name in the saluation. I have all the code to do everything
except that I can't figure out how to grab the first name. Any help
would be greatly appreciated. Thanks in advance.

Matt

 

[Rick Rothstein]

You probably want this...

=LEFT(A1,FIND(" ",A1&" ")-1)

but be advised that it (and most other solutions) will return the wrong
result for people whose first name has a space in it (such as Mary Ann).

 

[Rick Rothstein]

Whoops! You wanted this in VB code, right? Sorry, try this...

FirstName = Split(Range("B10").Value)(0)

 

[Matt]

You are right there is a space. For instance, if the name was United
States, there would be a space in between the name. I ran your vba
text, but it is only bringing in the first letter of the first word -
"U". Am I missing something?

 

[ker_01]

If your data is well formatted, you might try something like (untested):

left(namestring,find(" ",namestring))

I don't recall offhand, you might have to shorten the length by 1 to not
include the space itself, e.g.
left(namestring,find(" ",namestring)-1)

This only works if you have single first names and single last names. If
your data includes some with prefixes (Dr, Mrs, etc.) or two-word first names
"Carol Anne Carpenter" then this solution isn't likely to be sufficient
without additional data cleaning.

HTH,
Keith

 

[Rick Rothstein]

FirstName = Split(Range("B10").Value)(0)
That is not possible... unless there was a space between the "U" and the "n"
or unless you Dim'med the FirstName variable like this...

Dim FirstName As String * 1

which I think would be highly unlikely. Show us the code you are using (not
just my one-liner, but the code procedure you have it in).