Google Sheets regexextract first letter of each word

Question

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

Tanaike · Accepted Answer · 2019-05-19T06:20:00.723

7

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

=IF(A1="","",JOIN("",ARRAYFORMULA(REGEXEXTRACT(SPLIT(A1," "),"\b[\w]"))))

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

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

Added:

As an other, it uses LEFT instead of REGEXEXTRACT.

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

edited May 19 '19 at 06:20

answered May 19 '19 at 06:09

Tanaike

Yes, both works, I use the first example. I haven't used split and join before, I need to study that, its very handy. Tanks a bunch – Ralph Schipper May 19 '19 at 06:35
@Ralph Schipper Thank you for replying. I'm glad your issue was resolved. When the value is splitted by ``SPLIT``, the value is an array. So I used ``ARRAYFORMULA``. – Tanaike May 19 '19 at 06:49

score 4 · Answer 2 · answered May 19 '19 at 09:20

4

the true array formula would be:

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

answered May 19 '19 at 09:20

player0