Hi Richard,

Your teaching makes so easy for me to split bad data and to replace and minipulate data; however, this next scenario frustrates me, which is:

I've inherited and now have to fix a Table with Staff data that looks like this:

ID StaffName      
1  DeonRiley
2  RicardoRosti
3  KarynJones
4  BevCramer
5  StevePlessis
6  JockHobbs
7  MelSmithy
8  PeachesGedore
etc. over 10000 records

As you can see the above bad data the First and Last names are joined into one string with no spaces or any delimeter except, the UCase letter of the surname.

What process would I use to Parse the first and second part of the name into separate FirstName and LastName fields.

Thank you for your help with this Richard.

Kindest regards,

Deon

---

Reply from Richard Rost:

That's not going to be easy, but it's not impossible. You would have to create a custom function in VBA and loop down the field looking for the first capital letter (either by ASC char value or with a string). Once you have that, you can use the rest of my techniques to split the field. If you can't figure out how to do it, let me know and I can put the function together for you. Probably take me about half an hour.