Help

Combining two formulas

Topic Labels: Formulas
Solved
Jump to Solution
2990 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Deborah_Bensted
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
AlliAlosa
10 - Mercury
10 - Mercury

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!

See Solution in Thread

2 Replies 2
AlliAlosa
10 - Mercury
10 - Mercury

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