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

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

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

Need a bit of help in figuring out how Excel is evaluating a particular formula? It's easy to figure out if you use the ...

Discover MoreIf you want to add a dynamic statement to a worksheet that indicates how many rows are in a data table, you might be at a ...

Discover MoreYou might wonder how you can calculate an IRR (internal rate of return) when the person repaying the loan pays different ...

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

2018-03-19 01:42:14

Junior

Thanks, bro!

2017-11-23 04:25:21

Stephen Bench-Capon

Keep up the good work.

Stephen

2014-07-18 07:00:10

Michael (Micky) Avidan

is Unnecessary.

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator

“Microsoft®” MVP – Excel (2009-2015)

ISRAEL

2014-07-17 09:44:01

Andrew

Application.Volitile

to

Application.Volatile

!!!

2013-11-04 14:51:47

Bryan

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

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.

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.

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

Copyright © 2018 Sharon Parq Associates, Inc.

## Comments