Full names converted to initials

Mashtonsmith

Board Regular
Joined
Oct 1, 2004
Messages
164
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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?
 
Upvote 0
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
 
Upvote 0
Hi guys

Both Vog and Edokhotnik's method works but they are not picking up the 3rd initial where there are 3 names.
 
Upvote 0
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)))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top