Standardizing Names via a Formula

gkllc

New Member
Joined
Oct 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,832
Office Version
  1. 365
Platform
  1. Windows
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?
 
Solution

gkllc

New Member
Joined
Oct 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,323
Messages
5,547,256
Members
410,782
Latest member
Colart
Top