Full names converted to initials

[Mashtonsmith]

Hi,

I have a large spreadsheet with first name/s held within single cells.

i.e. John David
William Peter
Alice

I need to convert these into initials i.e. JD and WP.

A B
1 John David JD
2 William Peter WP
3 Alice A

Is there an easy way to do this?

Thanks

 

[T. Valko]

Hi,

I have a large spreadsheet with first name/s held within single cells.

i.e. John David
William Peter
Alice

I need to convert these into initials i.e. JD and WP.

A B
1 John David JD
2 William Peter WP
3 Alice A

Is there an easy way to do this?

Thanks

Is two the max number of names in any one cell?

 

[VoG]

Try

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

 

[Mashtonsmith]

I've looked and there are quite a few with 3 first names.
I'll also test your formula VOG.

 

[edokhotnik]

Hi,

I have a large spreadsheet with first name/s held within single cells.

i.e. John David
William Peter
Alice

I need to convert these into initials i.e. JD and WP.

A B
1 John David JD
2 William Peter WP
3 Alice A

Is there an easy way to do this?

Thanks

Give something like this a try:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 92px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana">John David</TD><TD>JD</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana">William Peter</TD><TD>WP</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana">Alice </TD><TD>A</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=IF(MID(A1,FIND(" ",A1),1)=" ",LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1))</TD></TR><TR><TD>B2</TD><TD>=IF(MID(A2,FIND(" ",A2),1)=" ",LEFT(A2,1)&MID(A2,FIND(" ",A2)+1,1),LEFT(A2,1))</TD></TR><TR><TD>B3</TD><TD>=IF(MID(A3,FIND(" ",A3),1)=" ",LEFT(A3,1)&MID(A3,FIND(" ",A3)+1,1),LEFT(A3,1))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

HTH, Ed

 

[Mashtonsmith]

Hi guys

Both Vog and Edokhotnik's method works but they are not picking up the 3rd initial where there are 3 names.

 

[VoG]

Try

=IF(ISERROR(FIND(" ",A1)),LEFT(A1,1),TRIM(LEFT(A1,1)&MID(A1,FIND(" ",A1&" ")+1,1)&MID(A1,FIND(" ",SUBSTITUTE(A1&" "," ","~",1))+1,1)))

 

[Mashtonsmith]

Vog - love your work.

Thanks all.

 

[Mashtonsmith]

Actually I do have one more query please..

Some of these name cells have loads of blank spaces after the names.

i.e. John SmithBBBBBBBBBBB

Where B represents a blank space and the amount of blanks is inconsistent.

How do I remove the blanks without removing the blank between the names..??

I normally wouldn;t bother but all these blanks are inflating the file size.

 

[VoG]

Try

=TRIM(A1)