CODEWITHSUNDEEP
regexgoogle-sheetsarray-formulasgoogle-sheets-formulagoogle-sheets-query
Ralph Schipper
Ralph Schipper

Reputation: 741

Google Sheets regexextract first letter of each word

how do I display the first letter of each word in let's say cell A1:

Example:

A1= Foo bar

A1= Foo

in the first example, I want to display "Fb"

in the second example, I want to see "F"

If A1 = empty I don't want to show anything

I tried this:

=REGEXEXTRACT(A1;"^.")

that shows only the first letter

Upvotes: 4

Views: 8273

Answers (2)

player0
player0

Reputation: 1

the true array formula would be:

=ARRAYFORMULA(IF(LEN(A1:A); 
 SUBSTITUTE(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(REGEXEXTRACT(SPLIT(A1:A; " "); 
 "\b[\w]"))); ; 999^99)); " "; ); ))

0

Upvotes: 4

Tanaike
Tanaike

Reputation: 201388

How about this? Please think of this as just one of several answers.

Modified formula:

=IF(A1="","",JOIN("",ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1," "),"\b[\w]"))))
  1. When the cell "A1" is empty and not empty, "" and the first letters of each word are put, respectively.
  2. Split the value to each word using SPLIT.
  3. Retrieve the first letter from each word using REGEXEXTRACT and ARRAYFORMULA.
    • Regular expression of \b[\w] was used.
  4. Join each array using JOIN.

Result:

enter image description here

References:

If this was not the result you want, I apologize.

Added:

As an other, it uses LEFT instead of REGEXEXTRACT.

Modified formula:

=IF(A1="","",JOIN("",ARRAYFORMULA(LEFT(SPLIT(A1," ")))))

Reference:

Upvotes: 8

Related Questions