Strip out @domainname.co.uk from Contact

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

Find more posts tagged with

Forms

Formulas

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.