2

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

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.

enter image description here enter image description here enter image description here

  • Thank you - this is perfect! – Henry Petch Nov 13 at 11:21
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"&" ")," ")))

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.