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.
Next, theLEFT functionextracts 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.
The result is an array of ASCII numbers.
We now have what we need to isolate capital letters only.
This array is the value assigned to the variablecapitals.
Finally, the formula concatenates the capital letters incapitalsinto a single text string using theTEXTJOIN function.
Thedelimiteris set to an empty string (""), so no additional characters are inserted.
Theignore_emptyargument is given as 1 so that TEXTJOIN will ignore any empty values in thecapitalsarray.
The value fortextcomes from cell B5, and serves as the source text for the formula.
The result is an array of every character in the source text.
That means a capital letterin any locationwill survive and appear in the final result.
Solving problems like this used to require insanely complex formulas because many key functions (i.e.
TEXTSPLIT, FILTER, SEQUENCE, UNIQUE, SORT, etc.)
As a result, the workarounds were ridiculously complicated.
Older versions of Excel do not contain TEXTSPLIT or FILTER.
MID returns an array of all characters in the text.
Separately, ROW and INDIRECT are used to create another numeric array:
This is the clever bit.
The numbers 65 to 90 correspond to theASCII codesfor all capital letters between A-Z.
MATCH then returns either a number (based on a position) or the #N/A error.
Numbers represent capital letters, so theISNUMBERfunction is used together with theIF functionto filter results.
TEXTSPLIT can split text into rows or columns.
TEXTJOIN Function
The Excel TEXTJOIN functionconcatenatesmultiple values together with or without a delimiter.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
For example, =MID(“apple”,2,3) returns “ppl”.
CODE Function
The Excel CODE function returns a numeric code for a given character.
LEN will also count characters in numbers, but number formatting is not included.
Related videos
How to use CHAR and CODE functions
