|
Last initial
I am trying to figure out how to extract intials from a name.
The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Hi!
Try this: =UPPER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)) Biff "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
In case of line wrap: (which I see in OE)
The last FIND function has a space between the quotes: ...........FIND(" ",A1)+1)+1,1)) Biff "Biff" wrote in message ... Hi! Try this: =UPPER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)) Biff "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Biff
Works like you knew it would. I knew I had to look for the second space but never would have figured that out. I will study it and try to figure out the )+1)+1,1)) stuff. Thanks a million for the help!!! Mike Rogers "Biff" wrote: Hi! Try this: =UPPER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)) Biff "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Try:
=UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,1)+3,1)) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Biff
Now how do I get rid of the #Value! error if A1 is empty? Mike Rogers "Biff" wrote: In case of line wrap: (which I see in OE) The last FIND function has a space between the quotes: ...........FIND(" ",A1)+1)+1,1)) Biff "Biff" wrote in message ... Hi! Try this: =UPPER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)) Biff "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Damon
Work great, thanks for the help. One more thing, how do I get rid of the #value! error when A1 is empty? Mike Rogers "Damon Longworth" wrote: Try: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,1)+3,1)) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Wrap the formula in an IF statement. Something similar to:
=if(iserror(YourFormula),"",YourFormula) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... Damon Work great, thanks for the help. One more thing, how do I get rid of the #value! error when A1 is empty? Mike Rogers "Damon Longworth" wrote: Try: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,1)+3,1)) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Got it, I used "IF" and "ISTEXT" to test the cell first, and it works.
Thanks again for the help Mike Rogers "Mike Rogers" wrote: Damon Work great, thanks for the help. One more thing, how do I get rid of the #value! error when A1 is empty? Mike Rogers "Damon Longworth" wrote: Try: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,1)+3,1)) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
I figured it out, I used an "IF" with "ISTEXT" to test the cell first.
Thanks for all the help. Mike Rogers "Mike Rogers" wrote: Biff Works like you knew it would. I knew I had to look for the second space but never would have figured that out. I will study it and try to figure out the )+1)+1,1)) stuff. Thanks a million for the help!!! Mike Rogers "Biff" wrote: Hi! Try this: =UPPER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)) Biff "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
Damon
Thanks for the followup Mike Rogers "Damon Longworth" wrote: Wrap the formula in an IF statement. Something similar to: =if(iserror(YourFormula),"",YourFormula) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... Damon Work great, thanks for the help. One more thing, how do I get rid of the #value! error when A1 is empty? Mike Rogers "Damon Longworth" wrote: Try: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,1)+3,1)) -- Damon Longworth 2006 East Coast Excel User Conference April 19/21st, 2006 Holiday Inn, Boardwalk Atlantic City, New Jersey Early Bird Registration Now Open!! www.ExcelUserConference.com 2006 UK Excel User Conference Summer, 2006 London, England "Mike Rogers" wrote in message ... I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers |
Last initial
On Fri, 27 Jan 2006 19:55:26 -0800, "Mike Rogers"
wrote: I am trying to figure out how to extract intials from a name. The names are in one column in the format of "Frank A Right". I have the first initial extracted and the middle initial extracted, but I can't seem to get the first letter of the last name. So far I have: =UPPER(LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)). What would I add to this to get the last initial? Thanks Mike Rogers If you want a simpler formula, you could download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ And then use a regular expression and his MCONCAT function: =MCONCAT(REGEX.MID(A1,"\b\w",{1,2,3})) It will return null strings for any absent entries instead of errors. --ron |
Last initial
If all your names follow the format firstname space singleinitial space lastname then you can use this formula which will return a blank if A1 is blank =UPPER(MID(A1,SEARCH(" ? ",A1&" x ")+3,1)) More generally if you want to return the first letter (in upper case) of the last word in a text string (as in your case) irrespective of what goes before you could use =UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)))) which also returns blank if A1 is blank -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505962 |
Last initial
On Sat, 28 Jan 2006 06:35:15 -0600, daddylonglegs
wrote: If all your names follow the format firstname space singleinitial space lastname then you can use this formula which will return a blank if A1 is blank =UPPER(MID(A1,SEARCH(" ? ",A1&" x ")+3,1)) More generally if you want to return the first letter (in upper case) of the last word in a text string (as in your case) irrespective of what goes before you could use =UPPER(LEFT(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)))) which also returns blank if A1 is blank Of course, that only returns the initial of the last name. To return all three initials concatenated together, as was apparent from the OP's initial posting, would require a much longer conventional formula, which would be more difficult to adapt to the various possible naming configurations. That's one of the advantages of regular expressions. --ron |
Last initial
Ron Rosenfeld Wrote: Of course, that only returns the initial of the last name. To return all three initials concatenated together, as was apparent from the OP's initial posting, would require a much longer conventional formula, which would be more difficult to adapt to the various possible naming configurations. That's one of the advantages of regular expressions. --ron Thanks Ron I didn't read the initial post carefully enough, hence I'd already edited my reply before your post, sorry :( I posted a revised formula but of course if you have MOREFUNC I'm sure the REGEX function would be superior -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=505962 |
| All times are GMT +1. The time now is 08:29 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com