Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulas

Google Sheets ArrayFormula to get INITIALS of arbitrary length name

Sample sheet.

As the title says, given a column of arbitrary number of words of arbitrary length, Want a single ArrayFormula to get the first letters of all words in the said column.

I have tried two methods, seen in sample sheet.

1) Using SPLIT and ARRAYFORMULA, can get it one cell but cannot extend down column. 2) Using 2 REGEXEXTRACT, can get for first 2 initials and extend down

But is it possible to get for arbitrary number of words for whole column using ArrayFormula.

Is it possible to use REGEXEXTRACT to return the first letters of many words?

Solution

  • This replaces every word with the captured first letter

    =ARRAYFORMULA(UPPER(REGEXREPLACE(A1:A6,"(\w)\S*\s?","$1")))