Extracting First Name from cell with first name and last name

M

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
 
R

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).
 
R

Rick Rothstein

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

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

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?
 
K

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
 
R

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top