How to truncate multiple email addresses left of @

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

Tips and Tricks

Parse

Transformation

Accepted answers

Hi @FogoFortitude

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

All comments

Hi @FogoFortitude

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)

[Domain_Name] = RIGHT([Email_1],LEN([Email_1])-FIND([Email_1],'@'))

Quick Links

Tags

This months top contributors

atcodedog05 19598

Qiu 15867

MarqueeCrew 13708

apathetichell 13703