Skip to main content

Hi there


After searching here I have successfully created formulas to manipulate an email field and now I want to know if those formulas can be combined so I only need one extra field.


I get email addresses like this …


teacher1@plc.nsw.edu.au

pupil2@students.acc.edu.au

pupil3@student.ncc.vic.edu.au

teacher@covenant.nsw.edu.au

pupil5@chairo.vic.edu.au

pupil6@student.ncc.vic.edu.au

pupil7@students.acc.edu.au

pupil8@students.acc.edu.au

teacher@frsa.asn.au

teacher@acc.edu.au


and I want to end up with JUST the domain and with the “student” and “students” part stripped out (e.g. acc.edu.au, or frsa,asn.au


I can end up with the domain by using this …


RIGHT(email, SUM(LEN(email), -FIND("@", email)))


And I can get rid of the “student” and “students” by using this …


SUBSTITUTE(

SUBSTITUTE(

{email},

“students.”,

“”

),

“student.”,

“”

)


Is there anyway of joining these two so that I only have one field? I tried the “&” but I just end up with two domains.


Many thanks! And thanks to those who post here so that I was even able to get this far.

You can definitely do this. Just replace the field {email} in your second formula with the entirety of your first formula (in the below example I removed the SUM() function as you can get to the same result without it.


SUBSTITUTE(SUBSTITUTE(RIGHT({email}, LEN({email}) - FIND("@", {email})), "students.", ""), "student.", "")


Hope this helps!

You can definitely do this. Just replace the field {email} in your second formula with the entirety of your first formula (in the below example I removed the SUM() function as you can get to the same result without it.


SUBSTITUTE(SUBSTITUTE(RIGHT({email}, LEN({email}) - FIND("@", {email})), "students.", ""), "student.", "")


Hope this helps!


That is GREAT. Thanks you so, so much. Of course I should have realised that it is replacing what you want replaced.


So much appreciate all the knowledge and willingness to share it here.


Deb

Reply


Terms & Conditions