Hi guys. I was hoping to have some advice on how to sort out some
values in an excel column,
where the field values are entered differently. I have values like:
ABC_XYZ_XXX0000012345
ABC_XXX0000012345
XXX0000012345
0000012345 and also blank ones.

I need to standardize em all to XXX0000012345.

I've tried formulas like
=IF(B1 < "",(RIGHT(B1, SEARCH("_XXX", B1)-1)))

but I totally don't know what I'm doing. I know it's pretty pitiful.
I did the IF because of the blanks causing #VALUE! errors.
I'll also have to do another one to add XXX to the numeric ones.

Can anyone offer some ideas on the best ways to filter out this kind of
crud
with formulas or macros? Eventually I need to use this spreadsheet
with these crazy values
and compare it versus another spreadsheet with XXX0000012345 values
entered consistently.
I was going to import them into Access and create a form/report for the
auditors here.

If anybody could help me out that would be so great! I'm a
mainframe/unix programmer now
doing access/vb on the fly. It's been a while since I had to use
msoffice docs and import them into access.