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

Pulling Initial Letters from a String

Written by Allen Wyatt (last updated June 10, 2023)
This tip applies to Excel 97, 2000, 2002, and 2003


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

This formula will handle, properly, anything from 0 to 4 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 Initials1(Raw As String) As String
    Dim Temp As Variant
    Dim J As Integer

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

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

This code will work in any version of VBA starting with Excel 2000. 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:

=Initials1(A1)

If you are using a version of Excel that doesn't support the Split function, then the following code will work just fine, as well:

Function Initials2(Raw As String)
    Dim p As Integer

    Application.Volatile
    Initials2 = ""
    p = 0
    Do
        Initials2 = Trim(Initials2) & Mid(Raw, p + 1, 1)
        p = InStr(p + 1, Raw, " ")
    Loop Until p = 0
End Function

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 (8661) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Quickly Changing Document Windows

When working with multiple documents at the same time, you often have a need to move from one document to another. Here's ...

Discover More

Selecting a Suggestion with the Keyboard

Excel tries to anticipate what you want to type into a cell, particularly when it comes to entering formulas. Here are ...

Discover More

Creating Two Versions of the Same Document

You may often need to create two versions of the same document, one with everything and the other with a subset of what ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Using Named Formulas or Constants

An easy way to create a name for a formula or constant value. The name can then be used in other formulas or for ...

Discover More

Placing Formula Results in a Comment

Excel won't allow you to directly or automatically insert the results of a formula into a cell's comment. You can, ...

Discover More

Counting Jobs Completed On a Date

When you store the date and time in a single cell, it can be a bit confusing to count how many cells contain a particular ...

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 0 + 7?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon 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.