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

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

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