Standardizing Names via a Formula

[gkllc]

I have data that downloads into excel from a cloud based product that has the customer names captured in 2 standards depending on the download report.
Fname, LName or Lname Fname (yes that's a space between lname & fname)
I need to standardize as to run reports on the data between the two downloads that have no other overlapping field columns.
Though I can do this easily via text to columns I'm trying to create a template that pivots and calculates the data in a way that the users only have to copy the raw data into a tab and other tabs do the work for them.
I'm also trying avoid macro's if possible.
Is there any formula that replicates the text to column functionality as to break out the Fname & Lname uniquely on a sheet?

I've played around with Substitute but I'm still stuck with names in reverse order....

I suspect I'm macro bound but thought I'd ask -

Thank you

 

[StephenCrump]

Welcome to the Forum!

If you have a nice neat space-separated LNAME FNAME in A1, you can easily split out using formulae:

LNAME: =LEFT(A1,FIND(" ",A1)-1)
FNAME: =RIGHT(A1,LEN(A1)-FIND(" ",A1))

But what are the chances your database is this neat? How is it going to accommodate names like Billy Ray Cyrus, Beyonce, Sir Elton John?

 

[gkllc]

95% are standardized but a few names have credentials after them but those are consistent in the 2 files -

Thanks - will give this a try & report back