Extracting First Name from cell with first name and last name
in [Programming]
|
Prev: Open VBA Editor?
Next: Select the UNIQUE vendors name
From: Matt on 23 Apr 2010 16:21 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
From: Rick Rothstein on 23 Apr 2010 16:57 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 (MVP - Excel) "Matt" <mattsonnier(a)gmail.com> wrote in message news:18861f38-6adc-4dfd-b6ad-f9ab3a566a73(a)g30g2000yqc.googlegroups.com... > 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
From: Rick Rothstein on 23 Apr 2010 17:00 Whoops! You wanted this in VB code, right? Sorry, try this... FirstName = Split(Range("B10").Value)(0) -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:eQ4YVey4KHA.6108(a)TK2MSFTNGP06.phx.gbl... > 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 (MVP - Excel) > > > > "Matt" <mattsonnier(a)gmail.com> wrote in message > news:18861f38-6adc-4dfd-b6ad-f9ab3a566a73(a)g30g2000yqc.googlegroups.com... >> 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 >
From: Matt on 23 Apr 2010 17:15 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?
From: ker_01 on 23 Apr 2010 17:18
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 "Matt" wrote: > 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 > . > |