Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to truncate multiple email addresses left of @

FogoFortitude
7 - Meteor

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 
5 REPLIES 5
JohnJPS
15 - Aurora

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
7 - Meteor

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
7 - Meteor

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

 

 

KaneG
Alteryx Alumni (Retired)

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

rahamrahimi
5 - Atom

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

 

 

Labels