You are viewing limited content. For full access, please sign in.

Question

Question

Extract domain name from email with Forms Formula

asked on February 21, 2024 Show version history

Hi all, 

Not sure if Forms Formulas have what it takes but I am trying to extract a users domain name from their email e.g shan@outlook.com . It would be something similar to textafter in excel. Can forms formula do that? I've looked through the formulas but haven't had any luck.

 

Thanks

 

 

0 0

Answer

SELECTED ANSWER
replied on February 22, 2024 Show version history

If you are just trying to find everything after the @ character, that is very doable with formulas.

We can start with the FIND formula to find the location of the @ character in the string.  

=FIND("@",email_variable)

 

This returns the index position of the first @ character in the string from the email_variable.  The downside here is that is the email_variable is blank or doesn't include an @ character, it will give an error, which is less than ideal.  One way around that is contatenating an @ character to the end of the email_variable to ensure it always has at least one @ character.

=FIND("@",email_variable&"@")

 

Now we know the position of the first @ charater in the field.  From that, we can use the MID formula to get everything from that character and after.  We actually want everything from 1 position after the @ character, so we can add 1 to the FIND formula's result.  That gives us the start of the position to pull out of the string, and then we just need to get the end, which we can just make a wide assumption and say 999 or some other number that we know will be well after the end of the string.  Put it all together, and it'll look something like this: 

=MID(email_variable,FIND("@",email_variable&"@")+1,999)

 

The formula now returns everything from after the @ character in the email address.

And because we took the added step of adding the extra @ character to the end of the string, we don't get an error from the formula if the email doesn't have an @ character within it - because the formula is actually working, it's just grabbing everything after the end of the string.

I hope this helps.  Have a nice day!

 

P.S. - you should probably edit your post and change it from a Discussion to a Question, so that you can mark it as Answered when you have a solution you like.

3 0
replied on February 22, 2024

Thanks Matthew, thats a great solution, I looked over the find formula, but used in combination the way you have is great.

 

Thanks for taking the time to write the detailed explanation.

1 0
replied on January 21

@████████ Are you able to find everything before the @ with a formula?
Thanks,
Pete

0 0
replied on January 21

Yes, @████████

We can use the FIND() formula to find the index position of the first occurrence of a substring. 
This formula: =FIND("@",email_variable,1) says "find the first occurrence of @ that appears in email_variable on or after index 1 and return the index of that first occurrence.
If we combine that with the LEFT() formula that lets us take the first x number of characters from a string, it can give us everything before that @ character.  We do need to subtract 1 from the index returned, since we want everything before that character but not including it.
The full formula would be like this:

=LEFT(email_variable, FIND("@",email_variable,1)-1)

 

1 0
replied on January 22

@████████ that worked!  Thank you!  

Do you have any ideas on how to add "PISD\" to the formula? 

I am trying to parse the username out of the email and then set another field as "PISD\username"

0 0
replied on January 22

You should be able to concatenate with either the CONCATENATE() formula or the & symbol.

So modifying the earlier formula as either this:

=CONCATENATE("PISD\",LEFT(email_variable, FIND("@",email_variable,1)-1))

or this:

="PISD\"&LEFT(email_variable, FIND("@",email_variable,1)-1)

should work.

I didn't jump in and test them, but I've done it a lot of times, so I'm pretty sure those will work.

3 0
replied on January 22

It worked!  I appreciate your knowledge sharing.
Pete

1 0
replied on January 22

You're very welcome.

0 0
replied on May 28

Sorry just revisiting this, how would you go backwards and get everything BEFORE the @ symbol. I thought it might have been as simple as changing the 999 to -999 to read backwards, or is there a whole new function needed?

 

thanks

 

0 0
replied on May 28

I haven't plugged this into a form to test, but I'm pretty sure it'll work.

 

This formula is saying to find the @ character and take everything after it. 

=MID(email_variable,FIND("@",email_variable&"@")+1,999)

 

We should be able to get everything before it like this. 

=LEFT(email_variable,FIND("@",email_variable&"@"))

 

Note that while the first formula will return blank if there is no @ symbol in the original text, the second formula will return the full text in that case.

0 0
replied on May 28

Fantastic thank you. I read that this is all Open Formula, does that mean when researching for formulas etc, it would be best to look up Open Formula solutions?

 

0 0
replied on May 29 Show version history

I would say they are only roughly based on Open Formula - I don't think there is a perfect match to Open Formula standards.  I would rely first on the Laserfiche help documentation, second on Laserfiche Answers, and third on external information about Open Formula.

1 0
replied on June 1

Ok great thank you

0 0

Replies

replied on May 29, 2024

Here is another example using the Split function, the last argument is either everything before or after the @ symbol - 1 or 2

=INDEX(SPLIT(email_var, "@"), 2)

3 0
replied on June 2, 2024

Awesome, thanks Ryan!

0 0
replied on February 22, 2024

Hi,
 

I believe if you use a regular expression in forms, it requires the user to input it the way the regular expression is set.

I can be wrong but here's my recommendation.

Pass the email address to workflow. Store the value into a token. Then modify the token and apply the regular expression. I don't know if you want to pass it back to forms or store it in SQL.

1 0
replied on February 22, 2024

Thanks Jasciel, 

 

This will be handy for workflow as well! At the moment I am trying to filter users from the starting form hence the need to do it in formulas, but thanks for this it definitely will come in handy!

 

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.