Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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 November 11, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


9

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 unavoidably becomes longer:

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

Remember that this is a single formula, even though it is shown here on multiple lines. 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. The following is one rather simple example:

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

    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, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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

Drawing Borders

Adding borders around cells is a common formatting task. You can make the task more intuitive by actually drawing the ...

Discover More

Setting a Default Workbook Window Size and Zoom Level

In order to make working with a workbook comfortable, you often need to set both a window size and a zoom level for the ...

Discover More

Reducing File Sizes for Workbooks with PivotTables

Need to reduce the size of your workbooks that contain PivotTables? Here's something you can try to minimize the ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Functions that Can Access Closed Workbooks

When creating a workbook, you can include formulas that reference data stored in other workbooks. Some functions will ...

Discover More

Entering Formulas in Excel

The primary way you signify that you are entering a formula is to start a cell entry with an equal sign. The equal sign ...

Discover More

Developing Reciprocal Conversion Formulas

When converting between measurement systems, you might want to use two cells for each type of measurement. Make a change ...

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 five more than 3?

2023-11-25 15:33:32

J. Woolley

Re. the JoinAsText function in my most recent comment below, there was an error associated with recursion. Please locate the following statement
    text = text & JoinAsText(Delimiter, IgnoreEmpty, item) & Delimiter
and replace it with these statements
    Dim temp As String
    temp = JoinAsText(Delimiter, IgnoreEmpty, item)
    If Not (IgnoreEmpty And temp = "") Then text = text & temp & Delimiter
This has been corrected in My Excel Toolbox.
See https://sites.google.com/view/MyExcelToolbox/


2023-11-17 18:07:31

J. Woolley

My Excel Toolbox now includes the following function to combine values, arrays, and/or cell ranges into a delimited text string:
=JoinAsText(Delimiter,IgnoreEmpty,Values,...)
This function matches Excel 2019's TEXTJOIN; see https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
If Delimiter is a null string (""), the result is the same as CONCAT.
Here is an abbreviated version:

Function JoinAsText(Delimiter As String, IgnoreEmpty As Boolean, _
    ParamArray Values() As Variant) As String
    Dim valu As Variant, item As Variant, text As String
    For Each valu In Values
        If IsArray(valu) Then
            For Each item In valu
                text = text & JoinAsText(Delimiter, IgnoreEmpty, item) _
                    & Delimiter
            Next item
        ElseIf IsError(valu) Then
            text = text & CStr(valu) & Delimiter
        ElseIf Not (IgnoreEmpty And valu = "") Then
            text = text & valu & Delimiter
        End If
    Next valu
    If text <> "" Then JoinAsText = Left(text, (Len(text) - Len(Delimiter)))
End Function

Notice this function is recursive.
See https://sites.google.com/view/MyExcelToolbox/


2023-11-15 06:36:19

Mike J

An alternative solution for users of earlier versions of Excel (tested using Excel2010) is to download UDFs for CONCAT and TEXTJOIN.
They work perfectly with J.Woolley's SplitText function.
Use Ctrl+Shift+Enter to convert to array formula.

UDF for CONCAT() is available here:
https://old.reddit.com/r/excelevator/comments/8w7b5p/udf_concat_textrange1_textrange2_concatenate/

UDF for TEXTJOIN() is available here:
https://old.reddit.com/r/excelevator/comments/5movbv/udf_textjoin_delimeter_ignore_blanks_valuerange/

I'm unsure about TEXTJOIN, but if using SplitText, the Trim() function does not seem to be required.


2023-11-14 11:44:39

J. Woolley

Re. my most recent comment below, if your version of Excel does not include TEXTJOIN or CONCAT here is a clever trick that permits use of CONCATENATE instead (with manual intervention). To get initials from a name in cell A1:
1. Enter the following formula in cell B1 (for example):
    =LEFT(SplitText(TRIM(A1)," "),1)
2. Select cell B1 and press F2 then Ctrl+A then F9 then Enter.
3. Select cell B1 and press F2 again, then edit the formula to replace curly brackets { } with parentheses ( ) and add CONCATENATE after the equal sign, then press Enter.
For example, if cell A1 contains the following text
    Rajeev    Kumar Pandey
after step 2 cell B1 should contain the array constant formula
    ={"R","K","P"}
and step 3 should convert that to the formula
    =CONCATENATE("R","K","P")
which returns the value
    RKP
See https://trumpexcel.com/concatenate-excel-ranges/


2023-11-12 11:10:19

J. Woolley

As noted by Allen, Alex Blakenburg's excellent formula currently requires Excel 365 because it uses TEXTSPLIT. If you don't have Excel 365, My Excel Toolbox includes the SplitText function described in my earlier comment here: https://excelribbon.tips.net/T013906_Splitting_Cells_to_Individual_Columns.html
Using SplitText, Alex's formula can be changed to
=TEXTJOIN("",TRUE,LEFT(SplitText(A1," "),1))
or with TRIM as discussed in my previous comment below
=TEXTJOIN("",TRUE,LEFT(SplitText(TRIM(A1)," "),1))
This formula can be simplified by using CONCAT instead of TEXTJOIN:
=CONCAT(LEFT(SplitText(TRIM(A1)," "),1))
I believe TEXTJOIN and CONCAT both require Excel 2019 or later.
See https://sites.google.com/view/MyExcelToolbox/


2023-11-12 10:47:56

J. Woolley

The last formula in my previous comment below had an error. It should have been
    =TEXTJOIN("",TRUE,LEFT(TEXTSPLIT(TRIM(A1)," "),1))
Sorry about that.


2023-11-11 11:36:23

J. Woolley

Re. the Tip's Initials function, it should be noted that VBA's Trim function does not work the same as Excel's TRIM function. Excel's TRIM removes all spaces from text except for single spaces between words. VBA's Trim only removes leading and trailing spaces. Therefore, if Raw has 2 words separated by 2 spaces, the following statement
    Temp = Split(Trim(Raw))
will return 3 substrings, not 2. However, the 2nd substring is null (zero-length), so the macro doesn't care because
    Left(Temp(J), 1)
returns the null string for this case.
This issue can be avoided by replacing the first statement above with
    Temp = Split(WorksheetFunction.Trim(Raw))
For the same reasons, Alex Blakenburg's function could be changed to
    =TEXTJOIN("",TRUE,LEFT(TEXTSPLIT(TRIM(A1," "),1))
but it matters less because the 2nd argument of TEXTJOIN is TRUE (ignore empty).


2023-11-11 08:47:26

Allen

Awesome formula, Alex, for those using the version of Excel with Microsoft 365.


2023-11-11 08:27:46

Alex Blakenburg

This will produce the same result as the Macro Function Initials
=TEXTJOIN("",TRUE,LEFT(TEXTSPLIT(A1," "),1))


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.