To be brutally honest, that's got to be the understatement of the century! A number of years ago, one of the largest banking corporation in the country bought out a smaller, regional bank, and needed to move the data on these 'new' clients into their own system. The problem, of course, was making the names and addresses come into formatting alignment with their own system. They assigned a team of six experienced programmers, who worked on algorithms to automate this process for six months, and finally gave up! The problem was that there is an almost infinite number of possible combinations involved in something like this!
Originally Posted by weejas
Just think about it! You've mentioned names like R OTTESON andJ&T BOTIFAN, but what about Alan Hale, Jr, Thurston Howell, III, and Dr. Ben Casey? The possibilities go on and on and on, like the Energizer Bunny!
You can try to automate the process, as weejas suggested, but in the end you're going to have to go through the records, one by one, and check them manually. The problem, of course, is that there's been no discipline on the Excel, input side, and that that app was designed poorly, cramming first and last names into one field.
And in the end, you may spend more time and money trying to automate this than you would if you simply hired what they call 'production data input' people. That's what the bank above ended up doing.
Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007