Strip out @domainname.co.uk from Contact

Phil Smith

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

 

 

oj6_projmgr

You could use the RIGHT function to remove the @domainname.co.uk part

Then use the REPLACE function to turn that . into a space

J. Craig Williams

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