Cleaning up the name field (1 Viewer)

[jn18]

In given table name is formatted as:

HERNANDEZ JR, FELIX / BRADY, ANDEL

and I need to make it to "Felix Hernandez" How would I be able to clean up "/" , "JR," in my query.

 

[Mile-O]

Is it only this one name you need to clean up?

If so, you could just use an UPDATE query.

 

[jdraw]

This is not trivial nor are you the first person to see this issue. Many people have made careers out of "cleansing data".
You really have to see if there are patterns in the input data. You need a good sampling of the data you will be receiving/have received.
From the sample you have shown:

You can use the Split() function to separate the name fragments at the commas --provided that's the only separator being used.

Things like " Jr " " Sr " "Esq"... are considered suffixes and could be handled as a group or separate case.

You seem to have a regular pattern here --- LastName [suffix], FirstName

You may get some ideas here.

Good luck.

 

[jn18]

This HERNANDEZ JR, FELIX / BRADY, ANDEL is one name in the table and I want to change it to Felix Hernandez.

 

[Mile-O]

As I said, an UPDATE query.

Change value to Felix Hernandez. Set criteria as the field in question = HERNANDEZ JR, FELIX / BRADY, ANDEL

 

[jn18]

This is just one employee in one raw, I have whole column with all names like this and need to clean up different scenarions

HERNANDEZ JR, FELIX / BRADY, ANDEl
LAZARINI, JUAN
COLON, ANTHONY N
ROGERS, ALPHONSO D / GUZMAN-MARTINEZ, PORFIRIO
DEJESUS, RUBEN R / REYES, CARLOS

anything after "/" are their supervisor name and can be removed in my new field. Output needs to be:

Felix Hernandez
Juan Lararini
Anthony colon
Alphonso Rogers
Ruben Dejesus

 

[Mile-O]

Bit messy, as just cobbled it together, but this may help.

Personally, I think it would be better if the forename and surname were stored in separate fields.

 

[jn18]

Thank You!

 

[jn18]

It worked sometimes just management wants things and accroding to them its the best way to display we can not do anything but give what they want.