Two Middle Names in to first letter

[nawas]

Hi All,

I've a list of lengthy names with two middle names eg: "Jenan Ibrahim Darwaish Al Darwaish" what i wanted is to convert in to "Jenan I D Al Darwaish"

Tried some formulas but it didn't work.. appreciate your support..


Thanks a lot.

[Richard Buttrey]

Hi,

This is clearly a much simplified example and doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

[nawas]

Thanks Richard,

And as requested attached a sample sheet.. just wanted to trim middle names in to its first letters.

cheers..

[canapone]

Hi

Hi,

you could try

=LEFT(A2,FIND(" ",A2))&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,300)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),200,300)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),300,300)),50)


Regards

[Glenn Kennedy]

Do they ALL have two middle names??

[vlady]

As additional to Glenn question. How about two First name and two middle name and some times two family name.
e.g
Robert James San Jose Dela Cruz - Robert James S.J. Dela Cruz