## Create name initials

Filed in Excel, Misc | Comments(13)

Email This article to a Friend

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

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

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)

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

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; […]

Create a list of dates with blanks between quarters in excel

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
During 2012 this website had almost 1 200 000 visitors, a 95% increase. It got hit by the google algorithm […]

Find positive and negative amounts that net to zero in excel

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]
Question: I have a text name coloumn, there will be only 1 value to be listed in 1 coloumn in […]

How to create random numbers, text strings, dates and time values

In excel the RAND() function returns a number greater than or equal to 0 (zero) and less than 1. Combining […]
Copying or moving cells is probably one of the most common activities in excel. Here is how to quickly select […]

Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]

### 13 Responses to “Create name initials”

### Leave a Reply

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

<code>your formula</code>

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