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

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

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

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”.

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?


Thank you. That was very helpful and worked.