Extract last word from a cell that includes the '@' character.

[toddy00]

Hi guys.

I found some brilliant advice on extracting the last cell from a cell of data in this thread from 2012. The user Rober Mika provided this formula for extraction of the last word from a cell:

=UPPER(TRIM(RIGHT(SUBSTITUTE(TRIM(X1)," ",REPT(" ",99)),99)))

This works great for most circumstances, however I have rows of data in which I am trying to extract twitter handles, meaning the last word begins with the @ symbol, 2 example rows of data below for reference:

Architectural DigestVerified account @ArchDigest
Architecture for...Verified account @archforhumanity

When I apply the formula above to this data, it extracts the last two words (instead of only the last word only) so the results look like this:

ACCOUNT @ARCHDIGEST
ACCOUNT @ARCHFORHUMANITY

Is there any amendment I can make to the formula that would extract only the last word (which will always begin with @ for my purposes of extracting the twitter handle from the data)?

Many thanks for your time and any assistance you can offer!

 

[XOR LX]

Hi.

If there's only ever one occurrence of a "@" within the string:

=MID(A1,FIND("@",A1)+1,LEN(A1))

If there may be several occurrences of a "@" within the string, but the desired extraction will always immediately follow the last such occurrence:

=TRIM(RIGHT(SUBSTITUTE(A1,"@",REPT(" ",LEN(A1))),LEN(A1)))

Regards

 

[toddy00]

Thank you XOR LX! That solved my problem perfectly. Much appreciated

 

[XOR LX]

No worries! Glad to help.

Cheers!