# Extracting initials from a name

#### rbsanders

##### New Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### just_jon

##### Legend
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),"")

=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),"")

#### IML

##### MrExcel MVP
Same caveats as JJ:
=LEFT(A2)&MID(A2,FIND("#",SUBSTITUTE(A2," ","#")&"#")+1,1)&MID(A2,FIND("#",SUBSTITUTE(A2," ","#",2)&"#")+1,1)

#### Yogi Anand

##### MrExcel MVP
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),"")

#### Torta

##### New Member
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 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
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
ActiveCell.EntireColumn.Offset(0, 1).Insert
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

Replies
12
Views
180
Replies
23
Views
301
Replies
7
Views
125
Replies
3
Views
547
Replies
0
Views
105

1,195,595
Messages
6,010,632
Members
441,558
Latest member
lambierules

### 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.

### Which adblocker are you using?

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

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