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

If you’re tired of manually extracting the first letter of each word in Google Sheets, I have some exciting array formulas that will make your life easier. In this article, I will share two formulas that are both simple and effective.

The Traditional Method: SPLIT, LEFT, and JOIN

Before we dive into the array formulas, let’s quickly go over the traditional method of extracting the first letter of each word in Google Sheets. Usually, we follow these steps:

1. Use the SPLIT function to split the string into individual words.
   =SPLIT(A2, " ")
2. Use the LEFT function to extract the first letter from each word.
   =ArrayFormula(LEFT(SPLIT(A2, " "), 1))
3. Join the extracted letters to form an abbreviation.
   =JOIN("", ArrayFormula(LEFT(SPLIT(A2, " "), 1)))

This method works well for non-array formulas, but it becomes tricky when coding an array formula. So, let’s explore two alternative solutions that are much simpler.

Array Formula 1: The Easiest Way

If you don’t need to worry about case sensitivity, Array Formula 1 is the way to go. Here’s how it works:

1. Use the PROPER function to capitalize the first letter of each word.
   =PROPER(A2)
2. Use REGEXREPLACE to extract only the capital letters.
   =REGEXREPLACE(PROPER(A2), "[^A-Z]+", "")

This formula can be easily converted into an array formula by using the ArrayFormula function:
=ArrayFormula(REGEXREPLACE(PROPER(A2:A), "[^A-Z]+", ""))

Feel free to reuse this formula for your Google Sheets tasks. You can find more helpful tips and tricks on Crawlan.com.

Array Formula 2: For Case Sensitivity

If you need the extracted letters to maintain their original case, Array Formula 2 is perfect. Here’s how it works:

1. Filter out any blank rows in the range A2:A.
   =FILTER(A2:A, A2:A<>"")
2. Split the filtered strings into individual words.
   =ArrayFormula(SPLIT(FILTER(A2:A, A2:A<>""), " "))
3. Extract the first letter from each word.
   =ArrayFormula(LEFT(SPLIT(FILTER(A2:A, A2:A<>""), " "), 1))
4. Use the QUERY function to combine the extracted letters.
   =TRANSPOSE(QUERY(TRANSPOSE(LEFT(SPLIT(FILTER(A2:A, A2:A<>""), " "), 1))),,9^9)

To remove any white spaces between the joined letters, you can use the SUBSTITUTE function:
=ArrayFormula(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(LEFT(SPLIT(FILTER(A2:A, A2:A<>""), " "), 1))),,9^9), " ", ""))

If your range contains blank cells between values, you can use the formula below, which includes the IFERROR function to handle errors:
=ArrayFormula(SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(LEFT(IFERROR(SPLIT(A2:A, " ")), 1))),,9^9), " ", ""))

And there you have it! Two array formulas to extract the first letter of each word in Google Sheets. These formulas will save you time and effort, allowing you to focus on more important tasks in your spreadsheet.

I hope you find these formulas helpful. For more Google Sheets tips and tricks, be sure to visit Crawlan.com. Enjoy exploring the wonderful world of Google Sheets!