Extracting multiple words from a string into different column

CowCookie

I'm trying to extract Twitter handles from tweets because my data provider unfortunately doesn't separate out retweets and @mentions. I should be able to do this using the tweets themselves since all the information is contained in the text, but I'm having difficulties getting the formula correctly.

My table is set up with the following columns:

Link to Twitter account

Tweet text

I want to do the following:

1. Move the unique part of [link to Twitter account] to a new column called [source]
2. If [tweet text] contains a word starting with "RT @", move that word into a new column called [retweet].
3. If [tweet text] contains a word starting with just "@, even if it also contains a word starting with RT @", move that word into a new column called [mentions].

So for the following table:

Link to Twitter account	Tweet text
https://twitter.com/suestanton	I like burgers
https://twitter.com/jimsmith	Check out @bobsburgers
http://twitter.com/jillknox	RT @jimsmith Check out @bobsburgers

I'd get:

Link to Twitter account	Tweet text	Source	Retweet	Mention
https://twitter.com/suestanton	I like burgers	suestanton
https://twitter.com/jimsmith	Check out @bobsburgers	jimsmith		bobsburgers
http://twitter.com/jillknox	RT @jimsmith Check out @bobsburgers	jillknox	jimsmith	bobsburgers

I've tried numerous variations of IF/ELSEIF combinations but can't seem to get anything that works. Any ideas?

MarqueeCrew

Another crack at it:

IIF(contains([Tweet text],"@),
REGEX_Replace([Tweet text], ".*?@(\w+).*", '$1'),'')

Cheers,

Mark

Extracting multiple words.yxmd