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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Full[/td][td][/td][td=bgcolor:#70AD47]Initials[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mary Jane Louise Wilson[/td][td][/td][td=bgcolor:#E2EFDA]M J L Wilson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Peter John Thomas[/td][td][/td][td]P J Thomas[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mary Ellen Bernard[/td][td][/td][td=bgcolor:#E2EFDA]M E Bernard[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Robert Louis Stevenson[/td][td][/td][td]R L Stevenson[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Cher[/td][td][/td][td=bgcolor:#E2EFDA]Cher[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Alan Alexander Milne[/td][td][/td][td]A A Milne[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]William Edward Burghardt DuBois[/td][td][/td][td=bgcolor:#E2EFDA]W E B DuBois[/td][/tr]

[tr=bgcolor:#FFFFFF][td]William Edward Burghardt Du Bois[/td][td][/td][td]W E B D Bois[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John Jack Jim Jerycho Jack Jacob Joshua Jules Brown[/td][td][/td][td=bgcolor:#E2EFDA]J J J J J J J J Brown[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Osama Binladen[/td][td][/td][td]O Binladen[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John Fitzgerald Kennedy[/td][td][/td][td=bgcolor:#E2EFDA]J F Kennedy[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Martin Luther King[/td][td][/td][td]M L King[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Dummy[/td][td][/td][td=bgcolor:#E2EFDA]Dummy[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Mary Ellen Bernard-Stevenson[/td][td][/td][td]M E Bernard-Stevenson[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Carla Pugh[/td][td][/td][td=bgcolor:#E2EFDA]C Pugh[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Erica Elliott[/td][td][/td][td]E Elliott[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Xaviera Mason[/td][td][/td][td=bgcolor:#E2EFDA]X Mason[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Maryam Nichols[/td][td][/td][td]M Nichols[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Hollee Bradshaw[/td][td][/td][td=bgcolor:#E2EFDA]H Bradshaw[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jael Good[/td][td][/td][td]J Good[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Shaine Sellers[/td][td][/td][td=bgcolor:#E2EFDA]S Sellers[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Violet Mcclure[/td][td][/td][td]V Mcclure[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Jacqueline Hutchinson[/td][td][/td][td=bgcolor:#E2EFDA]J Hutchinson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Holly Hale[/td][td][/td][td]H Hale[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Susan Odonnell[/td][td][/td][td=bgcolor:#E2EFDA]S Odonnell[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Zoe Hood[/td][td][/td][td]Z Hood[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Stephanie Richards[/td][td][/td][td=bgcolor:#E2EFDA]S Richards[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Eve Keller[/td][td][/td][td]E Keller[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Emerald Garrett[/td][td][/td][td=bgcolor:#E2EFDA]E Garrett[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Erin Jane Mary Holcomb-Dustin[/td][td][/td][td]E J M Holcomb-Dustin[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Vanna Castaneda[/td][td][/td][td=bgcolor:#E2EFDA]V Castaneda[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Nichole Patrick[/td][td][/td][td]N Patrick[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Barbara Russell-Cliff[/td][td][/td][td=bgcolor:#E2EFDA]B Russell-Cliff[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Ria Cotton[/td][td][/td][td]R Cotton[/td][/tr]
[/table]

[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]