Find and extract words/names from a string beginning with specific character

I've got some rows in Google Sheets that contain strings. I want to extract certain parts from this string and enter them into another cell:
specifically, I want to extract words/names that begin with the character "@".

For example in the string "Hello @Henry how are you" I want the next cell along to display only "@Henry", removing all the other words.

There are also cases when there are multiple names beginning with @ and I would want all of these returned (preferably in separate columns).

Can anyone help me with this?

• Share the spreadsheet to make it easier to understand what's happening Nov 12, 2019 at 16:10
• Please allow access as well as edit your spreadsheet. At the moment it is totally blocked. I can not access. You can do this in the upper right corner, in a green bar. Nov 12, 2019 at 17:20
• Ahh sorry. Here you go. docs.google.com/spreadsheets/d/… Nov 12, 2019 at 17:39

The `LEFT` function is for you to find values from the left-to-right summation and the `RIGHT` function does just the opposite. The `FIND` function is to find at what position exactly text you want. It automatically starts counting characters for `LEFT` or `RIGHT`. And the `LEN` function is to count the total value of characters that exist in a cell, so you need to use it in the `RIGHT` function, because you need to count the total character value minus the position you are looking to separate.

You can also use this single robust formula in cell `B2`:

``````=IFERROR(ArrayFormula(if(LEN(A2:A),TRIM(SPLIT(
REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","\$1"&" ")," ")
),"")),"")``````

An even simpler formula can be used if you don't have any empty rows in between your answers:

``````=ArrayFormula(TRIM(SPLIT(
REGEXREPLACE(TRANSPOSE(SPLIT(TRANSPOSE(ArrayFormula(join("♣︎", A2:A))),"♣︎")),"(?:.?(@[A-Z][a-z]\b))|(.*)","\$1"&" ")," ")))``````

Using `split` + a `filter` you can extract the words with the pattern you need : `@ + upper case + whatever`

``````=transpose(
filter(
transpose(SPLIT(A2," ")),
REGEXMATCH(transpose(SPLIT(A2," ")),"@[A-Z]")
)
)
``````