Strip out @domainname.co.uk from Contact
Hi
I have a sheet with a column for 'created by' (for form entries) and need to be able to strip out the @domainname.co.uk text so that I'm just left with the individuals name in a separate column.
Ideally, I would also like to add a space between firstname.lastname at the same time.
example:
firstname.surname@domainname.co.uk to firstname surname
Thanks in advance.
Phil
Comments
-
You could use the RIGHT function to remove the @domainname.co.uk part
Then use the REPLACE function to turn that . into a space
-
In my opinion, REPLACE() is too clunky for this usage. Better is SUBSTITUTE()
The answer also is a bit vague as RIGHT() returns the part that is being discarded and LEFT() won't get the result without knowing how to find the @ symbol.
Part 1: Get the name from the left of the @ symbol
=LEFT([Created By]@row, FIND("@", [Created By]@row) - 1)
Part 2: Replace the "." with a " " space
=SUBSTITUTE(LEFT([Created By]@row, FIND("@", [Created By]@row) - 1), ".", " ")
Hope this helps.
Craig
Help Article Resources
Categories
Check out the Formula Handbook template!
