Last initial

[Guest]

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

 

[Biff]

Hi!

Try this:

=UPPER(LEFT(A1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND("
",A1)+1)+1,1))

Biff

 

[Biff]

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

 

[Guest]

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

 

[Damon Longworth]

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





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

 

[Guest]

Biff

Now how do I get rid of the #Value! error if A1 is empty?

Mike Rogers

 

[Guest]

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]

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

 

[Guest]

Got it, I used "IF" and "ISTEXT" to test the cell first, and it works.

Thanks again for the help

Mike Rogers

 

[Guest]

I figured it out, I used an "IF" with "ISTEXT" to test the cell first.
Thanks for all the help.

Mike Rogers

 

[Guest]

Damon

Thanks for the followup

Mike Rogers

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

 

[Ron Rosenfeld]

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

 

[daddylonglegs]

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

 

[Ron Rosenfeld]

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

 

[daddylonglegs]

Of course, that only returns the initial of the last name.

To return all three initials concatenated together, as was apparen
from the
OP's initial posting, would require a much longer conventional formula
which
would be more difficult to adapt to the various possible namin
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 alread
edited my reply before your post, sorry

I posted a revised formula but of course if you have MOREFUNC I'm sur
the REGEX function would be superio