Extracting Initials and surname from cell

[Hudco]

I have a column which contains first names and surnames in the cells. I want to extract the initials and the full surname from each cell. e.g. cell A1 will have Peter John Thomas which I want to convert to P J Thomas, cell A2 has Mary Jane Louise Wilson which I want to convert to M J L Wilson.

I have found plenty of help to get all initials but none leaving the surname intact but getting the first initial from multiple first names.

Thanks in advance

 

[Eric W]

This works for up to 3 initials. Many caveats based on the format of the names, especially with extra or doubled spaces. You may want to use a helper cell with =TRIM(A1) in it.

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1,"",LEFT(A1)&" ",LEFT(A1)&" "&MID(A1,FIND(" ",A1)+1,1)&" ",LEFT(A1)&" "&MID(A1,FIND(" ",A1)+1,1)&" "&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)&" ")&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

 

[Scott Huish]

This works with up to 5 elements per name.

=TRIM(LEFT(A1)&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),1,1,"")

Excel 2010
	A	B
1	Mary Jane Louise Wilson	M J L Wilson
2	Peter John Thomas	P J Thomas
3	Mary Ellen Bernard	M E Bernard
4	Robert Louis Stevenson	R L Stevenson
5	Cher	Cher
6	Alan Alexander Milne	A A Milne
7	William Edward Burghardt DuBois	W E B DuBois
8	William Edward Burghardt Du Bois	W E B D Bois
Sheet1

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1)&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),1,1,"")
B2	=TRIM(LEFT(A2)&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255)),1,1,"")
B3	=TRIM(LEFT(A3)&" "&LEFT(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A3," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",255)),255)),1,1,"")
B4	=TRIM(LEFT(A4)&" "&LEFT(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A4," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",255)),255)),1,1,"")
B5	=TRIM(LEFT(A5)&" "&LEFT(TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A5," ",REPT(" ",255)),255)),1,1,"")
B6	=TRIM(LEFT(A6)&" "&LEFT(TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A6," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A6," ",REPT(" ",255)),255)),1,1,"")
B7	=TRIM(LEFT(A7)&" "&LEFT(TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A7," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A7," ",REPT(" ",255)),255)),1,1,"")
B8	=TRIM(LEFT(A8)&" "&LEFT(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),255,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),510,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),765,255)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),1020,255)))&LEFT(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",255)),1275,255))))&REPLACE(TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",255)),255)),1,1,"")

 

[Hudco]

Okay, any advances on 5?
Thank you so much Eric and Scott.
Both worked except Scott yours left 2 spaces at the beginning which I will sit down and work out why.
To both of you and families, all the best for the Christmas and New Year.

Addendum - sorted Scott - my cell had indent on, not sure why but fixed.

 

[Eric W]

Glad we could help.

Just how many initials do you expect? Either of our formulas could be modified to allow more initials, they would need more terms added, so they'd get longer.

And Merry Christmas, and Happy Holidays to you too!

 

[jtakw]

Hi,

I was working on a solution for your problem and came up with a Very similar formula to what Scott did in Post # 12, but he had already posted his, so I didn't post mine.

But I couldn't resist trying to come up with a "shorter" formula that can achieve the same results, so I kept trying and this is what I came up with (good for up to 5 name elements):

Book1
	A	B
1	Mary Jane Louise Wilson	M J L Wilson
2	Peter John Thomas	P J Thomas
3	John Doe	J Doe
4	Mary Ellen Bernard	M E Bernard
5	Robert Louis Stevenson	R L Stevenson
6	Cher	Cher
7	Alan Alexander Milne	A A Milne
8	William Edward Burghardt DuBois	W E B DuBois
9	William Edward Burghardt Du Bois	W E B D Bois
Sheet395

Cell Formulas
Range	Formula
B1	=LEFT(A1)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1)),2)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1,2)),2)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1,3)),2)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1,4)),2)&REPLACE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),1,1,"")

Formula copied down.

 

[Hudco]

Thanks jtakw,

Shorter the better. Mind you our longest name is 3 first names but the best thing is all solutions account for a hyphenated name correctly which I couldn't find elsewhere (non-MrExcel) so still the foremost authority thanks to you guys and others.

So thanks again everyone, and seasons greetings.

Clyde

 

[jtakw]

Thanks jtakw,

Shorter the better...the best thing is all solutions account for a hyphenated name correctly which I couldn't find elsewhere (non-MrExcel) so still the foremost authority thanks to you guys and others.

So thanks again everyone, and seasons greetings.

Clyde

Glad you like it shorter, because I realize my formula can be shortened further, the first SUBSTITUTE is not needed, still accommodates up to 5 name elements:

Book1
	A	B
1	Mary Jane Louise Wilson	M J L Wilson
2	Peter John Thomas	P J Thomas
3	John Doe	J Doe
4	Mary Ellen Bernard	M E Bernard
5	Robert Louis Stevenson	R L Stevenson
6	Cher	Cher
7	Alan Alexander Milne	A A Milne
8	William Edward Burghardt DuBois	W E B DuBois
9	William Edward Burghardt Du Bois	W E B D Bois
10	Mary Ellen Bernard-Stevenson	M E Bernard-Stevenson
Sheet395

Cell Formulas
Range	Formula
B1	=LEFT(A1)&MID(A1,FIND(" ",A1&" "),2)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1,2)),2)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1,3)),2)&MID(A1,FIND(1,SUBSTITUTE(A1&1," ",1,4)),2)&REPLACE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),1,1,"")

 

[sandy666]

you can try PowerQuery
doesn't matter how many FNames will be there

Full		Initials
Mary Jane Louise Wilson		M J L Wilson
Peter John Thomas		P J Thomas
Mary Ellen Bernard		M E Bernard
Robert Louis Stevenson		R L Stevenson
Cher		Cher
Alan Alexander Milne		A A Milne
William Edward Burghardt DuBois		W E B DuBois
William Edward Burghardt Du Bois		W E B D Bois
John Jack Jim Jerycho Jack Jacob Joshua Jules Brown		J J J J J J J J Brown
Osama Binladen		O Binladen
John Fitzgerald Kennedy		J F Kennedy
Martin Luther King		M L King
Dummy		Dummy
Mary Ellen Bernard-Stevenson		M E Bernard-Stevenson
Carla Pugh		C Pugh
Erica Elliott		E Elliott
Xaviera Mason		X Mason
Maryam Nichols		M Nichols
Hollee Bradshaw		H Bradshaw
Jael Good		J Good
Shaine Sellers		S Sellers
Violet Mcclure		V Mcclure
Jacqueline Hutchinson		J Hutchinson
Holly Hale		H Hale
Susan Odonnell		S Odonnell
Zoe Hood		Z Hood
Stephanie Richards		S Richards
Eve Keller		E Keller
Emerald Garrett		E Garrett
Erin Jane Mary Holcomb-Dustin		E J M Holcomb-Dustin
Vanna Castaneda		V Castaneda
Nichole Patrick		N Patrick
Barbara Russell-Cliff		B Russell-Cliff
Ria Cotton		R Cotton

[SIZE=1]let
    C2R = List.Transform({97..122}, each Character.FromNumber(_)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Prefix" = Table.TransformColumns(Source, {{"Full", each "x " & _, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Prefix", "Full", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Full.1", "Full.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Text.Remove([Full.1], C2R)),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Trim, type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Trimmed Text",{"Custom", "Full.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns",{"Custom", "Full.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Initials"),
    #"Trimmed Text1" = Table.TransformColumns(#"Merged Columns",{{"Initials", Text.Trim, type text}})

in
    #"Trimmed Text1"[/SIZE]