**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

**Formula in cell B2:**

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

### Initials from first, middle and last names

**Array formula in cell B2:**

=MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), 1), 1)&" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), 2), 1)&" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), 3), 1)&" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), 4), 1)

How to enter an array formula

Copy cell B2 and paste down as far as needed.

### Create middle name initials

**Array formula in cell B2:**

=MID(TRIM(A2), 1, FIND(" ", TRIM(A2)))&IFERROR(MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), IF(2<=COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2)))))), 2, "")), 1)&".", "")&IFERROR(" "&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), IF(3<=COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2)))))), 3, "")), 1)&".", "")&IF(COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2))))))=1, "", " ")&MID(TRIM(A2), SMALL(IF(ISERROR(SEARCH(MID(" "&TRIM($A2)&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), 1+COUNT(IF(ISERROR(SEARCH(MID(TRIM($A2), ROW($1:$99), 1), " ")), "", ROW(INDIRECT("$1:$"&LEN(TRIM($A2))))))), 999)

How to enter an array formula

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

Initials.xlsx

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