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

Question

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?

Edit: dummy spreadsheet below:

https://docs.google.com/spreadsheets/d/1TupcKRHHi94Q3OLmZS0-eEWNwbEZF90gjymoyem3GFI/edit?usp=sharing

Answer 1

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.

Answer 2

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"&" ")," ")))

Answer 3

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]")
             )
)