Formula question

[miki2024]

Hi Alteryx community,

Any chance to get some tips on this:

I am managing a database with tons of inconsistent user emails/ initials. I want to standardize the user contact info by removing all the email domain names after "@". For example, for susan@abc.com, I want to find a formula that can allow me to remove the domain info @abc.com and only keep the initial susan for my database. Sometimes the initials are written as @susan and I want to remove the "@" before the initial.

Do you recommend using trim function or RegEx in this case?

Cheers,

Miki

[mceleavey · Accepted solution]

Hi @miki2024 ,

yes, I think Regex is your friend here.

I've built the following Regex string:

^\W*(\w+.*?)[@]

This can be broken down as:

^ - From the beginning of the line

\W* - take zero or more non-word characters (this includes the "@" if it is present)

(\w+.*?)[@] - take the string from the remainder of the word to the "@" sign and exclude the @.

I've attached the workflow example.

I hope this helps,

M

[flying008 · Accepted solution]

Hi, @miki2024 

Other way for you get your want:

Left(Trim([Text],'@'), FindString(Trim([Text],'@'), '@'))

[summit_view · Accepted solution]

Hi @miki2024 

Another way, using Regex_Replace: 

REGEX_Replace([text], "@*(.*)@.*","$1")

[miki2024 · Accepted solution]

thanks @flying008 this is perfect!