Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Pulling Initial Letters from a String.

Pulling Initial Letters from a String

Written by Allen Wyatt (last updated September 2, 2022)
This tip applies to Excel 2007, 2010, and 2013


11

Rajeev needs a formula that will extract the first letters of a series of words. For instance, if a cell contains the text "Rajeev Kumar Pandey" he would like to extract, into another cell, the letters "RKP". The number of words in series can vary from cell to cell.

There are a couple of ways that this task can be approached. It is assumed, to begin with, that you don't want to modify the structure of your worksheet by adding intermediate columns. This assumption precludes, as well, the use of the Text to Columns feature to split the original string into individual words.

The key to the problem is making sure that your formula can determine where the spaces are in the original string. You might think that a formula such as the following will do the job:

=LEFT(A1,1)&MID(A1,FIND(" ",A1,1)+1,1)&MID(A1,
FIND(" ",A1,FIND(" ",A1,1)+1)+1,1)

This formula works partially. It works just fine if the original string has two spaces separating three words. If there are any fewer words then the formula returns an error. If there are any more words, then it returns only the first letters of the first three words (it ignores anything after the third word).

This means that the formula needs to not only check for spaces, but handle errors if there are no spaces or if there are too few spaces. The error checking means that the formula becomes much longer:

=IF(ISERR(LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",
A1)+1)+1)+1)+1,1)),IF(ISERR(LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1)+1,1)),
IF(ISERR(LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)),
IF(ISERR(LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)),
IF(ISERR(LEFT(A1,1)),"",LEFT(A1,1)),LEFT(A1,1)
&MID(A1,SEARCH(" ",A1)+1,1)),LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)),
LEFT(A1,1)&MID(A1,SEARCH(" ",A1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1)+1,1)),LEFT(A1,1)
&MID(A1,SEARCH(" ",A1)+1,1)&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1)+1,1)
&MID(A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)
+1)+1)+1,1))

This formula will handle, properly, anything from 0 to 5 words in a string. It also assumes that the string doesn't start or end with a space and that it doesn't contain multiple numbers of spaces between words. If you want to handle a larger number of words or other potential complications (such as the number of spaces between words), then it is best to use a user-defined function.

There are any number of ways that a user-defined function could pull the leading characters from the words of a string. In fact, I received quite a few variations that accomplish the same thing. The following example, however, is perhaps the most concise code that I ran across:

Function Initials(Raw As String) As String
    Dim Temp As Variant
    Dim J As Integer

    Application.Volitile
    Temp = Split(Trim(Raw))

    For J = 0 To UBound(Temp)
        Initials = Initials & Left(Temp(J), 1)
    Next J
End Function

The Split function "tears apart" a string based on where spaces occur within it. The individual words in the string are placed into an array (in this case, Temp) where you can then access individual words. To use the function in your worksheet, simply use something like this:

=Initials(A1)

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8663) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Pulling Initial Letters from a String.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Making a Cell's Contents Bold within a Macro

When your macro is processing information in a worksheet, do you need to periodically make the contents of a cell bold? ...

Discover More

Importing Huge Data Files

Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here's ...

Discover More

Non-printing Page Borders

With your page border in place, you might be surprised if you don't see one side of the border (or all sides) print out ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Summing Digits in a Value

Want to add up all the digits in a given value? It's a bit trickier than it may at first seem.

Discover More

Moving the House Number to Its Own Cell

Excel is great at manipulating data, but sometimes it is difficult to figure out the best way to do the manipulation. ...

Discover More

Converting from Relative to Absolute

Addresses used in a formula can be either relative or absolute. If you need to switch between the two types of ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 2 + 5?

2022-09-05 05:25:26

Mike Hodkinson

Could this now be solve with the advent of =TEXTAFTER formula by the following

Enter in C3
Rajeev Kumar Pandey

Then say in H3 the following
=LEFT(C3,1)&LEFT(TEXTAFTER(C3," ",1),1)&LEFT(TEXTAFTER(C3," ",2),1)

to get the three initials

and yes can amend Textafter formula if not found use ""

Would appreciate your thoughts on this

Mike H


2021-08-20 11:09:30

Willy Vanhaelen

Correction: ... I posted 8th August ...


2021-08-20 11:04:18

Willy Vanhaelen

This macro is in fact identical to the one I posted 8th May except for the names of the variables. With Name and Names
the macro code becomes self explanatory:

Function Initials(Names As String) As String

Dim Name As Variant

For Each Name In Split(Names)
Initials = Initials & Left(Name, 1)
Next Name

End Function


2021-08-08 08:13:32

Willy Vanhaelen

Here is a shorter version of this tip's UDF:

Function Initials(raw As String) As String
Dim X As Variant
For Each X In Split(raw)
Initials = Initials & Left(X, 1)
Next X
End Function


2021-06-20 11:58:32

S. S.

This code is good for upto 20 words, where A1 is the cell with data.

=IFERROR(LEFT(A1,1),"")&IFERROR(MID(A1,FIND(" ",A1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1)+1,1),"")


2018-03-19 01:42:14

Junior

Thanks, bro!


2017-11-23 04:25:21

Stephen Bench-Capon

This is awesome. Thank you! I have done messy stuff to get round this before but that Initials function is great - it just seems to have a typo in volatile, but really, thanks very much!
Keep up the good work.
Stephen


2014-07-18 07:00:10

Michael (Micky) Avidan

To my opinion the: Application.Volatile
is Unnecessary.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL


2014-07-17 09:44:01

Andrew

Of course, that lean VBA Module works great BUT only if you correct the typo:

Application.Volitile

to

Application.Volatile

!!!


2013-11-04 14:51:47

Bryan

I forgot to mention: when using the named formula version, expanding to cover more initials is trivial. The hundredth initial would be =MID(A1,pos99+1,1).

Now what would be REALLY amazing is a formula version that accounts for any number of spaces. If it's possible it would use an array formula, but it's much beyond my powers.


2013-11-04 14:48:02

Bryan

The macro solution is clearly the best (and easiest) way to go here. Application.Volatile is unnecessary, and because I'm super OCD I'd change J = 0 to J = LBound(Temp). The function is non-volatile, and even though the lower bound of Split will probably forever be 0, it's just better practice to make as few assumptions as possible.

As for the formula version, you can shorten the first formula IMMENSELY by using IFERROR instead of IF/ISERR:

=IFERROR(LEFT(A1,1),"")&IFERROR(MID(A1,FIND(" ",A1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1,1),"")&IFERROR(MID(A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)+1)+1,1),"")

My version is 261 characters, a full 787 characters smaller than the one presented here -- that's a 75% reduction! I'm sure someone more clever than I could reduce it even more.

If I needed to do this for more than one cell, I would either use helper columns or named range formulas (which one I'd use depends on how lazy I am or how the data is later going to be used). Those poor FINDs are being called over and over! Here's how I would set them up:

pos1 = FIND(" ",A1)
pos2 = FIND(" ",A1,pos1)
pos3 = FIND(" ",A1,pos2)
pos4 = FIND(" ",A1,pos3)
int1 = LEFT(A1,1)
int2 = MID(A1,pos1+1,1)
int3 = MID(A1,pos2+1,1)
int4 = MID(A1,pos3+1,1)
int5 = MID(A1,pos4+1,1)
result = IFERROR(int1,"")&IFERROR(int2,"")&IFERROR(int3,"")&IFERROR(int4,"")&IFERROR(int5,"")

Then you just type in =result -- a whopping 6 characters (4, with autocomplete)!! Ok, perhaps I'm being a little facecious, since there are actually more formulas at play -- adding up all the characters used in the all named formulas, you get a grand total of 231, which is still a 30-character/11% reduction. And I bet it's a ton faster than the original, to boot.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.