| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove middle initial from "first name middle initial" | Excel Discussion (Misc queries) | |||
| First name, Middle Initial | Excel Discussion (Misc queries) | |||
| Ignoring Initial Articles in Field Sort | Excel Discussion (Misc queries) | |||
| Stripping Middle Initial from a Name | Excel Worksheet Functions | |||
| Extracting Last Name and First Initial | New Users to Excel | |||
Hybrid Mode
