I’m trying to get full names from email addresses in Google Sheets. The emails look like ‘[email protected]’. I used this formula:
=REGEXEXTRACT(A1, "(.*)\.(.*)@")
It splits the name into two cells, giving me ‘firstname’ and ‘lastname’ separately. But I want the whole name ‘firstname lastname’ in one cell.
I’ve been at this for hours and can’t figure it out. Does anyone know how to tweak the regex or use a different formula to get the full name in one go? Thanks for any help!
Having worked extensively with data cleaning in Google Sheets, I can suggest an alternative approach using a combination of SPLIT and CONCATENATE functions. Here’s a formula that should do the trick:
=CONCATENATE(SPLIT(LEFT(A1, FIND(“@”, A1) - 1), “.”))
This formula first extracts everything before the @ symbol, then splits it at the dot, and finally concatenates the results. It’s efficient and handles the firstname.lastname format well.
One advantage of this method is its flexibility. If you ever need to modify the output format (e.g., add a comma between last and first names), it’s easy to adjust. Just remember to test it thoroughly with your specific dataset to ensure it covers all scenarios.
I’ve dealt with a similar situation before, and I found a solution that might work for you. Instead of using REGEXEXTRACT, you can combine REGEXREPLACE with SUBSTITUTE to get the full name in one cell. Here’s the formula I used:
=SUBSTITUTE(REGEXREPLACE(A1, “(.)@.”, “$1”), “.”, " ")
This formula first removes everything after the @ symbol, then replaces the dot with a space. It’s straightforward and gives you the full name in one go.
One thing to keep in mind: if some email addresses don’t follow the firstname.lastname format, you might need to adjust the formula. But for most cases, this should work well. Let me know if you need any clarification on how it works!