How to truncate multiple email addresses left of @

[FogoFortitude]

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 

[JohnJPS · Accepted solution]

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!

[FogoFortitude · Accepted solution]

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 

[lrygiel]

In addition to the formula tool you can use the REGEX tool  (parse method). Try the regular expression (.*)@*

[KaneG]

Another option is that you could just use a Text2Columns tool on @...

[rahamrahimi]

 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],'@'))