Hi,
I am attempting to truncate a set of email addresses of varying length with the goal of returning only the string of characters left of the '@' character.
Examples of desired output:
xxxx@gmail.com = xxxx
stevejobs@gmail.com = stevejobs
I have code below which I would have thought would have worked, but for some reason the following part of the code (
(FindString(TRIM([EMAIL_ADRS_X]),'@')-1)
) seems to applies the first emails @ position to every new email record. Am I nesting my formula correctly? I feel that I have overlooked something fundamental here.
IF Contains([EMAIL_ADRS_X],'@') THEN LEFT(TRIM([EMAIL_ADRS_X]), (Length(TRIM([EMAIL_ADRS_X]))-(FindString(TRIM([EMAIL_ADRS_X]),'@')-1)) ) ELSE 'xxxxx' ENDIF
Solved! Go to Solution.
I think you're very close. I would try Substring from 1 to FindString on "@" ...
IF Contains([EMAIL_ADRS_X],'@') THEN Substring([EMAIL_ADRS_X],1,FindString([EMAIL_ADRS_X],'@')-1) ELSE 'xxxxx' ENDIF
Hope that helps!
Thanks @JohnJPS, That did the trick, I found that I had to tweak it a little to get the results I needed with losing the first character and the last charcter in the string left of the @.
IF Contains([EMAIL_ADRS_X],'@') THEN Substring([EMAIL_ADRS_X],0,FindString([EMAIL_ADRS_X],'@')) ELSE 'xxxxx' ENDIF
In addition to the formula tool you can use the REGEX tool (parse method). Try the regular expression (.*)@*
Another option is that you could just use a Text2Columns tool on @...
Here is way to get domain names of email addresses:
Trimming ".com"
[Email_1] = LEFT( [Email],FIND([Email],'.',FIND([Email],'@') )-1)
Selecting everything after @
[Domain_Name] = RIGHT([Email_1],LEN([Email_1])-FIND([Email_1],'@'))