Retrieve 1st Letters from Full Name

[Accusoft]

Hi

am using MS Office 2007

I have a column (eg: A) contains NAMES which consists of 1 word name (First name), 2 word names(First name & Last name) and 3 word names (First name , Middle name and Last name).

Eg.

Column A
=======
Xavier
xavier jose
Xavier jose kozhiparambil

etc....

i appreciate your help in obtaining result in

Column B
=======
X
X J
X J K

respectively ...... (i.e. X for Xavier; X J for Xavier Joseph; X J K for Xavier Jose Kozhiparambil)
in B

with 1 space between all Characters - all CAPS (irrespective of what the source case is)
Please refer to the Excel file for any clarification.

Please do help me asap

[FDibbins]

Try this, copied down...
=UPPER(LEFT(A5,1))&
IF(LEN(A5)-LEN(SUBSTITUTE(A5," ",))>=1, " "&UPPER(MID(A5,LEFT(FIND(" ",A5,1))+1,1)),"")&
IF(LEN(A5)-LEN(SUBSTITUTE(A5," ",))>1," "&UPPER(MID(A5,FIND(" ",A5,FIND(" ",A5,1)+1)+1,1)))

This will only work for up to 3 names

[TMS]

Or:

Please Login or Register  to view this content.

@Ford: you could put the UPPER around the whole formula rather than repeating it for each name.


Regards, TMS

[TMS]

@Ford; did you copy that formula up to row 3 for the 2 and 1 names?

[FDibbins]

Thanks for the catch Trevor, I thought I had copied upwards to test, but apparently not

Sooooo fixing that, and updating the UPPER as suggested, that becomes...
=UPPER(LEFT(A5,1)&
IF(LEN(A5)-LEN(SUBSTITUTE(A5," ",))>=1, " "&MID(A5,LEFT(FIND(" ",A5,1))+1,1),"")&
IF(LEN(A5)-LEN(SUBSTITUTE(A5," ",))>1," "&MID(A5,FIND(" ",A5,FIND(" ",A5,1)+1)+1,1),""))

But Trevor's is still slicker

[Accusoft]

I really appreciate your work Mr. Ford and Mr. TMS... thank a lot

Great job....

everything worked fine except a small difference.....

please refer the attachment, row 13

i couldn't guess, why the difference?

[FDibbins]

Becasue I had a "LEFT" in there that should not be there

=UPPER(LEFT(A13,1)&
IF(LEN(A13)-LEN(SUBSTITUTE(A13," ",))>=1, " "&MID(A13,FIND(" ",A13,1)+1,1),"")&
IF(LEN(A13)-LEN(SUBSTITUTE(A13," ",))>1," "&MID(A13,FIND(" ",A13,FIND(" ",A13,1)+1)+1,1),""))

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.