Google Sheets – How to Find and Extract Words from String with Specific Character

google sheetsgoogle-sheets-arrayformularegex

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

Best Answer

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

Related Topic