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

 

[nemmi69]

How about this?

Option Explicit
Sub ConvName()
Dim FullName As String
Dim TName As String
Dim TspcCount As Integer
Dim Cloop As Integer
Dim Tloop As Integer
Dim LastRowNo As Long
Dim FirstName As String
Dim ConvertedName As String


LastRowNo = ActiveSheet.Range("A1048576").End(xlUp).Row


For Tloop = 1 To LastRowNo
    FullName = ActiveSheet.Range("A" & Tloop).Value
    TspcCount = 0
    For Cloop = 1 To Len(FullName)
        If Mid(FullName, Cloop, 1) = Chr$(32) Then TspcCount = TspcCount + 1
    Next Cloop
    TName = FullName
    ConvertedName = ""
    Do While Len(TName) > 0
        If TspcCount > 0 Then
            FirstName = Left(TName, 1)
            ConvertedName = ConvertedName & " " & FirstName
            TName = Right(TName, Len(TName) - InStr(1, TName, " "))
            TspcCount = TspcCount - 1
        Else
            ConvertedName = ConvertedName & " " & TName
            TName = ""
        End If
    Loop
    ActiveSheet.Range("D" & Tloop).Value = ConvertedName
Next Tloop
End Sub

 

[Hudco]

Sorry, Nemmi69, I was looking for a cell formula.

Thanks anyway.

 

[excel_learnerz]

Hi,

Depending on the version of excel you are using (this feature might only be on O365 excel or excel 2016) so in column A1 you have names Peter John Thomas (and the rest of the names in that cell).
In cell B1 write P J Thomas then select the column and Flash fill (on the ribbon bar under Home, then on the right hand side click on the dropdown on "Fill" and select flashfill), it will do exactly as you ask for the whole range

 

[excel_learnerz]

*

(and the rest of the names in that column).​

 

[Eric W]

If you have Office 365 with the TEXTJOIN function:

	A	B	C
Mary Jane Louise Wilson	M J L Wilson
Peter John Thomas	P J Thomas
John Doe	J Doe
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

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]{=TEXTJOIN(" ",TRUE,IF(MID(" "&A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>" ","",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),IF(ISERROR(FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1))))),99,1))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Note that it could still fail with some 2 part last names, such as the last example.

 

[Hudco]

Thanks guys,
Probably my original post is not clear, the names are individually in a cell i.e. the first person is in A1, second in A2 etc. I am using Office Professional 2013.

So A1 has Peter John Thomas, A2 has Mary Jane Louise Wilson, A3 George Smith etc.

Eric W, I tried your suggestion but getting #NAME ? as the result.

 

[Eric W]

You're getting the #NAME error because Excel 2013 does not have TEXTJOIN. Since you don't want VBA, the next best option is Flash Fill as suggested in post #4 . Given the layout I showed in post #6 , you'd put "M J L Wilson" in B1 (no quotes), then keep the B1 cell selected, then press Control+E.

The only other option I can think of at the moment is a long, complicated formula that would work for a maximum of 3 initials. I don't have the time to figure it out right now, but perhaps tomorrow.

 

[jtakw]

Hi,

excel_learnerz's suggestion in Post # 4 and 5 should do what you asked, try it.

 

[Hudco]

Apologies, the solution I want is not for a one off conversion but in a template where a new workbook is created for each client where clients names (Mum, Dad, children etc.) are completed in full in a cells (say B20 to B25) and in other sheets in that workbook I need their names as initials and surname. I know I could just type their names into say 4 separate cells (three for first names and one for surname) but for various reasons it is easier to type the name in full in the one cell.