If you’re tired of manually extracting the first letter of each word in Google Sheets, I have the perfect solution for you! In this article, I will show you two array formulas that will make your life much easier.
A Tricky and Easy Array Formula
Let’s dive right in and explore the first array formula. This formula is incredibly simple to use and will save you valuable time. Now, let’s assume you have the following string in cell A2: “medium weight beam.” You want to extract the first letter of each word and join them to create an abbreviation like “mwb.”
Here’s how you can do it:
- Start by using the SPLIT function to split the string into three parts based on the space delimiter.
=split(A2," ")
- Next, use the LEFT function to extract the first letter from each word.
=ArrayFormula(left(split(A2," "),1))
Note: We use the ArrayFormula because we want to extract the first letter from more than one (split) word.
- Finally, combine the extracted letters using the JOIN function.
=JOIN("",ArrayFormula(left(split(A2," "),1)))
It’s advisable to wrap the formula with IFERROR to avoid errors in case there are no values in A2.
And there you have it! This non-array method is the best way to extract the first letter of each word in Google Sheets. But, there’s a catch. Since we used the JOIN function, it becomes challenging to create an array formula following the same method.
But don’t worry! I have two alternative solutions for you. One of them is even trickier and easier to use than the non-array one.
Two Array Formulas to the Rescue
I have two array formulas for you: Array Formula 1 and Array Formula 2. Let’s discuss them one by one.
Array Formula 1
If you don’t need to worry about the case sensitivity of the extracted letters, Array Formula 1 is your go-to option. It’s the easiest way to extract the first letter of each word in Google Sheets.
Here’s how you can use Array Formula 1:
- Start by using the PROPER function to capitalize the first letter of each word and make the rest of the letters small.
=proper(A2)
This will give you “Medium Weight Beam” as the result.
- Next, use the REGEXREPLACE function to extract only the capital letters from the above string.
=REGEXREPLACE(proper(A2),"[^A-Z]+","")
Since this formula doesn’t contain the JOIN function, you can easily turn it into an array formula.
=ArrayFormula(REGEXREPLACE(proper(A2:A),"[^A-Z]+",""))
This will give you the desired result of extracting the first letter of each word.
Array Formula 2
If you need a case-sensitive formula that preserves the original case of the letters, Array Formula 2 is the way to go.
Here’s how you can use Array Formula 2:
- Start by using the FILTER function to skip blank rows in the A2:A range.
=filter(A2:A,A2:A<>"")
- Split the filtered strings.
=ArrayFormula(split(filter(A2:A,A2:A<>"")," "))
- Extract the first letter from each word.
=ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1))
-
Use the QUERY function to combine the extracted letters.
- Use TRANSPOSE to change the orientation of the above formula result.
=TRANSPOSE(ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1)))
- Use QUERY to combine the letters.
=query(transpose(ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1))),,9^9)
- Transpose the result again.
=transpose(query(transpose(ArrayFormula(left(split(filter(A2:A,A2:A<>"")," "),1))),,9^9))
Now you have the array formula to extract the first letter from each word in Google Sheets!
But wait, there’s a slight issue. The Query function leaves white spaces between the joined letters. Don’t worry, you can use the SUBSTITUTE function 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 following formula instead. It includes the IFERROR function after the SPLIT to handle blank cells.
=ArrayFormula(substitute(transpose(query(transpose(left(iferror(split(A2:A," ")),1)),,9^9))," ",""))
And there you have it! You now know how to extract the first letter of each word in Google Sheets using array formulas.
Enjoy using these formulas and save yourself valuable time in your Google Sheets tasks. If you want to discover more useful tips and tricks, head over to Crawlan.com for more expert insights.
Thank you for reading, and happy spreadsheeting!
