If you are looking for an array formula to extract the first letter of each word in Google Sheets, find two formulas below. Among them, one is very tricky and easy to use.

To extract the first letter of each word, we usually follow the below steps in Google Sheets (I won’t follow it to write the array formula).

Let’s assume cell A2 contains the string “medium weight beam“.

I want to extract the letters “m”, “w”, and “b” and join them as an abbreviation like “mwb“.

Usually, we will solve the above problem USING SPLIT, LEFT, and JOIN as below and drag it down to apply to the string in the next row.

Steps (formula Explanation):-

1. We can use the SPLIT function to split the string into three parts based on the space delimiter.

=split(A2," ")

2. Then using the LEFT function, we can extract the first letter from each word.

=ArrayFormula(left(split(A2," "),1))

I’ve used the ArrayFormula within the above formula since we want to extract the first letter from more than one (split) word.

3. Just combine the result.

=JOIN("",ArrayFormula(left(split(A2," "),1)))

It is advisable to wrap the formula with IFERROR to avoid error incase of no values in A2.

The above is the best non-array method to extract the first letter of each word in Google Sheets.

But this formula has one problem.

Since we have used the JOIN function, we may find it difficult to code an array formula following the above method.

We can only use part of the formula that I’ll explain later.

Here I have two alternative solutions to the above problem. One is very tricky and easy to use than the above non-array one.

Two Array Formulas to Extract the First Letter of Each Word in Google Sheets

I’ve two formulas below titled Array Formula 1 and Array Formula 2.

I am recommending the array formula 1.

If you are not concerned about the case sensitivity of the extracted letters, use it. It is the easiest way to extract the first letter of each word in Google Sheets.

The Array Formula 1 will convert all the extracted letters to CAPS. If you want, you can make it SMALL (lower case).

Use the Array Formula 2 if you are looking for a case sensitive formula. I mean, the extracted letters will be the same as in the string. It won’t change the letters’ case.

Array Formula 1

Let’s consider the same string in cell A2, which is “medium weight beam”.

Here is the easiest way to abbreviate the above string in Google Sheets.

Steps:-

1. Use the PROPER function to make the first letter of each word capital and the rest of the letters small.

=proper(A2)

The result will be “Medium Weight Beam”.

2. Using REGEXREPLACE, we can extract only the capital letters from the above string.

=REGEXREPLACE(proper(A2),"[^A-Z]+","")

Since it doesn’t contain the JOIN function, we can easily make it an array formula.

=ArrayFormula(REGEXREPLACE(proper(A2:A),"[^A-Z]+",""))

Array Formula 2

To get one more array formula to extract the first letter of each word in Google Sheets, please follow the steps below.

Here, we will SPLIT the strings, then extract the first letter using LEFT. Instead of JOIN, we will use the QUERY to combine the extracted letters.

1. Let’s first FILTER the range A2:A to skip blank rows.

=filter(A2:A,A2:A<>"")

2. Split the filtered strings.

=ArrayFormula(split(filter(A2:A,A2:A<>"")," "))

3. Let’s extract the first letter in each word.

=ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1))

4. Using the QUERY, we can combine the extracted letters. I’ve got a detailed tutorial on the use of QUERY for joining strings here – The Flexible Array Formula to Join Columns in Google Sheets.

How it works?

We can use the HEADER of QUERY for this. We will use an arbitrarily large number in the header. So, the QUERY will consider all the rows in the range are headers and combine them into a single row.

4.1. Use TRANSPOSE to change the orientation of the above formula result.

=TRANSPOSE(ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1)))

4.2. Use the QUERY to combine the letters.

=query(transpose(ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1))),,9^9)

4.3. Again transpose it.

=transpose(query(transpose(ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1))),,9^9))

The above is the array formula to extract the first letter from each word in Google Sheets.

But wait. See the results. The Query leaves white spaces between the joined letters.

You can use REGEXREPLACE or SUBSTITUTE to remove them.

=ArrayFormula(substitute(transpose(query(transpose(left(split(filter(A2:A,A2:A<>"")," "),1)),,9^9))," ",""))

Note:- If you have blank cells between the values in A2:A, use the below formula. In this formula, I have omitted the FILTER and included IFERROR after the SPLIT.

=ArrayFormula(substitute(transpose(query(transpose(left(iferror(split(A2:A," ")),1)),,9^9))," ",""))

I hope you could understand how to extract the first letter of each word in Google Sheets.

Thanks for the stay. Enjoy!