Need formula to replace email domain name and period with a space

[dbradford (David Bradford)]

I have a text column that contains an email address. I woudl like to strip off the @domain.com and replace the period character with a space make a well formatted full name. Example tom.smith@domain.com, result in “Tom Smith”

I can get the domain name out with this formula: TRIM(SUBSTITUTE({Submitter’s Email},“@domain.com”, “”))

However, I am unable to remove the period in the username. As a test I reused the same formula and attempted to replace the “.” with a “P” like this TRIM(SUBSTITUTE({Submitter’s Email},“.”, “P”)). This results in a complete replacement of all characters with “P”.

[GCavin (Gilles Cavin - Reinventing Formulas in monday.com)]

Hi David,

I can confirm that there is a bug with the SUBSTITUTE function. Here is a workaround:

PROPER(LEFT(LEFT({Text}, FIND("@", {Text})-1),SEARCH(".",LEFT({Text}, FIND("@", {Text})-1))-1) & " " & RIGHT(LEFT({Text}, FIND("@", {Text})-1),LEN(LEFT({Text}, FIND("@", {Text})-1))-SEARCH(".",LEFT({Text}, FIND("@", {Text})-1))))

 

---

Curious to know how that same formula would look in the Advanced Formula Booster, the 3rd party app that makes it so easy to write formulas, does not use the Formula column but rather can write to any type of columns?

[dbradford (David Bradford)]

Thank you. That was very helpful and worked.