Abbreviate names or words

In this example, the goal is to create initials or acronyms with a formula using the data in column B as the source text. The formula should parse the text in column B, build a list of capital letters used to start words and join the capital letters together in a single text string. The article below explains 3 ways to do this. The first two methods require a current version of Excel. The last (and more complex method) is an array formula that will work in Excel 2019.

Modern formula #1

In the worksheet shown, the formula in cell D5 looks like this:

=LET(
text,B5,
chars,LEFT(TEXTSPLIT(text," ")),
codes,CODE(chars),
capitals,FILTER(chars,(codes>=65)*(codes<=90)),
TEXTJOIN("",1,capitals)
)

In brief, this formula splits the text in cell B5 into words, extracts the first letter of each word, removes all but the capital letters, and concatenates the result into a single string. The details work like this:

First, we use the LET Function to define four named variables (text, chars, codes, capitals) to make the formula easier to read and modify, as well as more efficient. The text variable is assigned the value in cell B5, and serves as the source text from which the capital letters will be extracted. Next, we assign a value to chars with this code:

LEFT(TEXTSPLIT(text," "))

The TEXTSPLIT function splits the text into an array of words using spaces (" ") as the delimiter. Next, the LEFT function extracts the first character from each word in the array created by TEXTSPLIT. The final result is an array of the first letters of each word.

Next, we create a value for codes using the CODE function:

CODE(chars)

Here, CODE returns a numeric ASCII value for each character in the chars array. The result is an array of ASCII numbers. We now have what we need to isolate capital letters only. This is done with the FILTER function:

FILTER(chars,(codes>=65)*(codes<=90))

The FILTER function filters the chars array to include only those characters whose codes are between 65 and 90, which correspond to the uppercase letters (A-Z) in ASCII. The result from FILTER is an array that contains only the capital letters that appear at the beginning of a word. This array is the value assigned to the variable capitals.

Finally, the formula concatenates the capital letters in capitals into a single text string using the TEXTJOIN function. The delimiter is set to an empty string (""), so no additional characters are inserted. The ignore_empty argument is given as 1 so that TEXTJOIN will ignore any empty values in the capitals array.

Modern formula #2

The formula below shows another way to solve this problem in a modern version of Excel:

=LET(
text,B5,
chars,MID(text,SEQUENCE(LEN(text)),1),
codes,CODE(chars),
capitals,FILTER(chars,(codes>= 65)*(codes<= 90)),
TEXTJOIN("",1,capitals)
)

As above, the LET function is used to declare variables, which are the same four as above: text, chars, codes, and capitals. The value for text comes from cell B5, and serves as the source text for the formula. The value for chars is defined by the following snippet:

MID(text,SEQUENCE(LEN(text)),1)

This is a fairly common method in more advanced Excel formulas to convert a text string to an array of characters. The result is an array of every character in the source text. Next, we use the FILTER function to preserve only capital letters as before:

FILTER(chars,(codes>= 65)*(codes<= 90))

The resulting array contains all the capital letters in text. Finally, we use the TEXTJOIN function to join the letters together:

TEXTJOIN("",1,capitals)

The main thing to note in this approach is that we don't bother with words, we simply collect and filter all the characters in the source text. That means a capital letter in any location will survive and appear in the final result.

Legacy Excel solution

The formulas above work well and I recommend you use them in a current version of Excel. I leave the example below mainly as a historical reminder of the state of Excel formulas not so long ago. Solving problems like this used to require insanely complex formulas because many key functions (i.e. TEXTSPLIT, FILTER, SEQUENCE, UNIQUE, SORT, etc.) were not available. As a result, the workarounds were ridiculously complicated.

Older versions of Excel do not contain TEXTSPLIT or FILTER. However, if you have Excel 2019, you have the TEXTJOIN function which can be used to solve this problem with a much more complex formula like this:

=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)),ROW(INDIRECT("65:90")),0)),MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),""))

Note: this is a traditional array formula and must be entered with control + shift + enter.

Working from the inside out, the MID function is used to cast the string into an array of individual letters:

MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)

In this part of the formula, MID, ROW, INDIRECT, and LEN are used to convert a string to an array of letters, as described here. MID returns an array of all characters in the text.

{"W";"i";"l";"l";"i";"a";"m";" ";"S";"h";"a";"k";"e";"s";"p";"e";"a";"r";"e"}

This array is fed into the CODE function, which outputs an array of numeric ASCII codes, one for each letter. Separately, ROW and INDIRECT are used to create another numeric array:

ROW(INDIRECT("65:90")

This is the clever bit. The numbers 65 to 90 correspond to the ASCII codes for all capital letters between A-Z. This array goes into the MATCH function as the lookup array, and the original array of ASCII codes is provided as the lookup value.

MATCH then returns either a number (based on a position) or the #N/A error. Numbers represent capital letters, so the ISNUMBER function is used together with the IF function to filter results. Only characters whose ASCII code is between 65 and 90 will make it into the final array, which is then reassembled with the TEXTJOIN function to create the final abbreviation or acronym.