## Create name initials

*Article updated on February 07, 2018*

**Table of contents**

- Initials from first and last names
- Initials from first, middle and last names
- Create middle name initials
- How to enter an array formula

### Initials from first and last names

### Initials from first, middle and last names

**Array formula in cell B2:**

Copy cell B2 and paste down as far as needed.

### Create middle name initials

**Array formula in cell B2:**

Copy cell B2 and paste down as far as needed.

### How to enter an array formula

- Select cell B2
- Click in formula bar

- Paste array formula in formula bar
- Press and hold Ctrl + Shift
- Press Enter

### Download excel *.xlsx file

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]### 14 Responses to “Create name initials”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I have some shorter formulas for you...

Initials from first and last names

------------------------------------

=LEFT(TRIM(A2))&" "&MID(TRIM(A2),FIND(" ",TRIM(A2))+1,1)

Initials from first, middle and last names

-----------------------------------------------------------------

=TRIM(LEFT(TRIM(A2))&" "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99)))&" "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),199,99)))&" "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),298,99))))

Create middle name initials

---------------------------------------------------

=CHOOSE(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ","")),TRIM(A2),SUBSTITUTE(TRIM(A2)&" "," "&TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99))&" "," "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99)))&". "),SUBSTITUTE(SUBSTITUTE(TRIM(A2)&" "," "&TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99))&" "," "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99)))&". ")," "&TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),199,99))&" "," "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),199,99)))&". "))

My "Initials from first and last names" formula is only 3 characters shorter (taking advantage of the LEFT functions default behavior); however my "Initials from first, middle and last names" formula is about half the size of yours and my "Create middle name initials" formula is a little more than 60% the size of yours.

Rick Rothstein (MVP - Excel),

Great, they worked perfectly!

Thanks for posting!

[...] How to create initials from a list of first, middle and last names [Get Digital help] [...]

Oscar, have I got a treat for you. Assuming that there will never be any cells with more than four names in it, here is an amazingly short formula (considering what has been posted earlier) that I have just come up with...

=LEFT(A2,FIND(" ",A2))&IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))>1,LEFT(TRIM(A3))&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),100,99)))&". ","")&IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=3,LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),199,99)))&". ","")&TRIM(LEFT(RIGHT(SUBSTITUTE(" "&A2," ",REPT(" ",99)),99),99))

Sorry, I forgot to mention... the formula is for creating middle name initials.

Okay, in addition to the much shorter formula for creating middle name initials that I just posted above, here is a shorter formula for the initials from first, middle, last names as well...

=TRIM(LEFT(A2)&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),100,99)))&" "&LEFT(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),199,99))))

Rick Rothstein (MVP - Excel),

Thank you for commenting!

I can´t get your formulas working, check out the attached file. Sheet 2 and column C and D.

Initialsv2.xlsx

I suspect wordpress removing characters.

@Oscar,

It looks like I had copied the wrong formula for the "creating middle name initials"... there was an A3 reference that should have been an A2 reference and there was an extra TRIM function call concatenated onto the part displaying the first initial... I remember having fixed both of those before posting, so I must have copied the wrong formula when I created my comment here. Sorry. Fixing those made the formula into the final form I meant to post, but that did not take care of all the problems though, as I did not account for extra leading and trailing spaces around the names (which is why the "initials from first, middle, last names" formula failed as well). Here are the fixed formula which should now work for you (longer than originally intended because of the added TRIM function calls to take care of the leading and trailing spaces, but still noticeably shorter than my previous short formulas from last year)...

Creating middle name initials

-----------------------------------------------------------

=LEFT(TRIM(A2),FIND(" ",TRIM(A2)))&IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))>1,LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99)))&". ","")&IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))=3,LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),199,99)))&". ","")&TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A2)," ",REPT(" ",99)),99),99))

Initials from first, middle, last names

-----------------------------------------------------------

=TRIM(LEFT(TRIM(A2))&" "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99)))&" "&LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),199,99))))

By the way, I noticed you had array-entered the formulas in the workbook you linked to in your last message... the above formulas do not need to be array-entered, they will work fine when normally entered.

Silly me... I have two occurances in my "Creating middle name initials" formula where I TRIM cell A2 only to then follow do that by removing all the spaces in cell A2. Removing those two unnecessary TRIM function calls shortens the formula by 12 characters without affecting the formulas ultimate action. Here is that revised formula...

=LEFT(TRIM(A2),FIND(" ",TRIM(A2)))&IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))>1,LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),100,99)))&". ","")&IF(LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))=3,LEFT(TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),199,99)))&". ","")&TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A2)," ",REPT(" ",99)),99),99))

Rick Rothstein (MVP - Excel),

I got your formulas working, thanks for posting!

[…] How to create initials from a list of first, middle and last names [Get Digital help] […]

[…] How to create initials from a list of first, middle and last names [Get Digital help] […]

[…] How to create initials from a list of first, middle and last names [Get Digital help] […]

[…] How to create initials from a list of first, middle and last names [Get Digital help] […]