Turning Names to Abbreviations

[Hoop71]

I am looking for a way to turn a cell with a name eg.

John Adams Smith

into:
J. A. Smith

The trick is that it still needs to function if there are only 2 names eg.

John Smith

Thanks for the help in advance... I got lost in the stings of formulas on this one.

 

[jasonb75]

Welcome to the board!

Could probably tidy this up a little, I was getting lost in there somewhere too.

Try

=IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=0,LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=1,LEFT(B2,1)&". "&RIGHT(B2,LEN(B2)-FIND(" ",B2,1)),LEFT(B2,1)&"."&MID(B2,FIND(" ",B2)+1,1)&"."&RIGHT(B2,LEN(B2)-FIND(" ",B2,FIND(" ",B2)+1)+1)))

Think it does what you need.

 

[jbeaucaire]

You looking for a formula in B1 that will take the value in A1 to display the conversion? Or a macro that will insert the new values into the original cells?

For a formula solution:

Excel Workbook
	A	B
1	John Adam Smith	J. A. Smith
2	John Smith	J. Smith
3	Jane Doe	J. Doe
4	J. Wilkes Booth	J. W. Booth
5	John W Booth	J. W. Booth
Sheet1

 

[Hoop71]

You guys ROCK!

UGhhhhhhhhhhh

Happy Exceling

 

[Hoop71]

How about for this variation?

Smith, Allen -> Smith, A.
Smith, Allen William -> Smith, A.W.

Again needs to work for 2 and 3 word names...

 

[jbeaucaire]

All in the same data set? Sounds very chaotic....

Excel Workbook
	A	B
1	John Adam Smith	J. A. Smith
2	John Smith	J. Smith
3	Jane Doe	J. Doe
4	J. Wilkes Booth	J. W. Booth
5	John W Booth	J. W. Booth
6	Smith, Allen	Smith, A.
7	Smith, Allen William	Smith, A.W.
Sheet3

 

[Hoop71]

WOW... in 45 seconds ... you are good.

Thanks so much