Apple Vision Pro is now available in the U.S.

Get first letter of each word in a single cell

Let's say I have my A1 cell with the name of a client (John Appleseed). I need a formula that prints JA.


I was using REGEX.EXTRACT with the expression [A-Z\s]+, but I can only get one occurence (J or A).


Keep in mind that A1 is not always filled only with 2 words, I could have John Appleseed S.L., John Chapman Appleseed...


Do any of you guys know a way of doing this?


Thanks,

Dani

MacBook Pro 15″, 14.2

Posted on Nov 20, 2023 4:59 AM

Reply
10 replies

Nov 20, 2023 9:18 AM in response to Danval31

A little closer in that you capture uppercase accented characters.




=CONCAT(REGEX.EXTRACT(A2,"[A-Z0-9|[À-Ú]]",0,0))


Systematical replacing all accented characters with unaccented equivalents via formula is not a trivial task, though if you have just a few different ones that are likely to occur then you could consider using SUBSTITUTE.



SG

Nov 20, 2023 5:31 AM in response to Wayne Contello

Also... if the list is a comma separated list in a single cell this will be very difficult without a script.


So I recommend copying the content of the cell and and pasting it into text editor and replace the commas with returns so there is one entry per row.


Then you can fill the formula I posted down to subsequent rows.


For the last examples of input you posted

John Appleseed S.L., John Chapman Appleseed


What is the expected output?

Nov 20, 2023 5:57 AM in response to Wayne Contello

Some examples with their expected output:


John Appleseed --> JA

John Appleseed S.L. --> JASL

John Chapman Appleseed --> JCA

John00 Appleseed --> J00A or JA (I don't care)

John Chapman Àppleseed --> JCÀ or JCA (I don't care)


And maybe I wasn't that clear in my original post, but in each cell there's only 1 client. The thing is that as you see in the examples below, not all clients are made of 2 words.

Get first letter of each word in a single cell

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.