Extracting initials from a name

rbsanders

New Member
Joined
Sep 23, 2003
Messages
10
Is there a formula that will extract the initials from a name? For example, from the name Robert E James, I would like only REJ to appear in the cell?
 

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).
You will note this will fail on some names, and is not designed for > 3 initials...
foo.xls
ABCD
1NameInits
2RobertEJamesREJ
3JonJustJJ
4AlfredENewmanAEN
5BGatesBG
6PeleP
7P.lePewPl
8
Sheet1


in B2, copied down: =LEFT(A2,1)&IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>=1,MID(A2,FIND(" ",A2)+1,1),"")&IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>=2,MID(SUBSTITUTE(A2," ","",1),FIND(" ",SUBSTITUTE(A2," ","",1))+1,1),"")

EDIT: added TRIM --
=LEFT(TRIM(A2),1)&IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))>=1,MID(TRIM(A2),FIND(" ",TRIM(A2))+1,1),"")&IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))>=2,MID(SUBSTITUTE(TRIM(A2)," ","",1),FIND(" ",SUBSTITUTE(TRIM(A2)," ","",1))+1,1),"")
 
Upvote 0
Same caveats as JJ:
=LEFT(A2)&MID(A2,FIND("#",SUBSTITUTE(A2," ","#")&"#")+1,1)&MID(A2,FIND("#",SUBSTITUTE(A2," ","#",2)&"#")+1,1)
 
Upvote 0
Or (again with upto a maximum of 3 initials as Just_jon mentioned) ...

=LEFT(A2)&IF(ISNUMBER(FIND(" ",A2)),MID(A2,FIND(" ",A2)+1,1),"")&IF(ISNUMBER(FIND(" ",A2,FIND(" ",A2)+1)),MID(A2,FIND(" ",A2,FIND(" ",A2)+1)+1,1),"")
 
Upvote 0
I realize this is an old post but I thought I'd share my solution for future searchers. It is a VBA macro solution that will work on a highlighted range of names and add a column to the right with the initials. Will work with different formats (e.g., [FIRST NAME] [MIDDLE NAME] [LAST NAME] or [LAST NAME], [FIRST NAME] [MIDDLE INITIAL]).


'**Purpose: Gets initials from full names in a highlighted range and adds them to a new column beside the full name
'** Works on multiple formats (extra spaces okay) -
'** John Doe
'** Doe, John
'** John H Doe
'** John H. Doe
'** John Harry Doe
'** Doe, John H
'** Doe, John H.
'** Doe, John Harry
Sub getInitials()
Dim cell As Range
Dim initials, fName, lName, mName, fullName As String
Dim colAdded As Boolean
Dim x, col As Integer

On Error GoTo err_handler
Const mInitial As String = "_" '**mInitial value is used as a placeholder if no middle name or initial is in the name value.
'**Change to whatever you like. "" for no placeholder.

'**Begin loop through each cell in user's selection
For Each cell In Selection
cell.Activate

'**checks if selection spans and has changed to a different column in order
'**to insert another column for the new intials values
If cell.Column <> col Then
colAdded = False
col = cell.Column
End If

If Not IsNull(cell.Value) And cell.Value <> "" Then
'**Adds a new column to the right of the selected active cell in order
'**to place new initials value and not overwrite other data
If Not colAdded Then
ActiveCell.EntireColumn.Offset(0, 1).Insert
colAdded = True​
End If

fullName = Trim(cell.Value)

'**If comma is in name value then assumes last name is first
If InStr(fullName, ",") Then
lName = Left(fullName, InStr(fullName, ",") - 1)
fName = Trim(Mid(fullName, InStr(fullName, ",") + 1))
If InStr(fName, " ") Then
mName = Trim(Mid(fName, InStr(fName, " ") + 1))
fName = Left(fName, InStr(fName, " ") - 1)​
Else
mName = mInitial​
End If
'**Otherwise no comma and assumes first name is first
ElseIf InStr(fullName, " ") Then
fName = Left(fullName, InStr(fullName, " ") - 1)
lName = Trim(Mid(fullName, InStr(fullName, " ") + 1))
If InStr(lName, " ") Then
mName = Trim(Left(lName, InStr(lName, " ") - 1))
lName = Trim(Mid(lName, InStr(lName, " ") + 1))​
Else
mName = mInitial​
End If​
End If
initials = Left(fName, 1) & Left(mName, 1) & Left(lName, 1)
ActiveCell.Cells.Offset(0, 1).Value = UCase(initials)
x = 0

'**The following 2 code lines are useful if your full name values are
'**in different formats and you would like to tidy things up

'**Uncommenting (removing the " ' " from) the following code will overwrite
'**highlighted name values with a standard convention of
'**[Last Name], [First Name] [Middle Name]
'ActiveCell.Value = StrConv(lName, vbProperCase) & ", " & StrConv(fName, vbProperCase) & _
IIf(mName = "_", "", " " & StrConv(mName, vbProperCase))

'**Uncommenting the following line will overwrite name values same as above but uses convention of
'**[First Name] [Middle Name] [Last Name] (only uncomment one or the other)
'ActiveCell.Value = StrConv(fName, vbProperCase) & IIf(mName = "_", " ", " " & _
StrConv(mName, vbProperCase) & " ") & StrConv(lName, vbProperCase)

Else
x = x + 1
'**Stop loop after 50 blank fields are encountered
If x = 50 Then Exit For​
End If​
Next cell

'**End at the top of the selection
Selection.Cells(1, 1).Activate
err_handler:

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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