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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

Mashtonsmith

Board Regular
Joined
Oct 1, 2004
Messages
164
I've looked and there are quite a few with 3 first names.
I'll also test your formula VOG.
 

edokhotnik

Board Regular
Joined
Nov 10, 2010
Messages
104

ADVERTISEMENT

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

Board Regular
Joined
Oct 1, 2004
Messages
164
Hi guys

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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

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

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

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
Top