Name, Date and Address Formats
 

Hi,


I have a number of different spreadsheets that I need to impirt into a
single database. Unfortunately some of the information is in different
formats.


Would anyone be able to provide me with a solution that can turn:


A B Brown
ABC Brown
*A B C*Brown*
Brown, A B
Brown, Anthony Bernard
Brown Anthony Bernard
Mr A B Bernard


Into separate fields for title, forename, initials & surname


On a similar theme I also need to do much the same thing with
addresses:


1 Victoria Road, Victoria, Victoriashire, VC1 1VC


etc


And lastly (for now anyway) I also have some dates that I need to
convert from:


01012004 (dd/mm/yyyy)
112004 (d/m/yyyy)
20040101 (yyyy/mm/dd)
200411 (yyyy/m/d)


to 01/01/2004 (dd/mm/yyyy)


Hope someone can help


cheers

Your solution won't be easy. "Brown Anthony Bernard" would be especially
difficult. How is the program to know that "Brown" is a surname, not a forename?

My first suggestion is to see if you can create a cell which identifies the
source of the data, and therefore defines the format. Assuming the formatting is
consistent within each source, reformatting it isn't all that difficult. You
would parse the string with a combination of LEFT, RIGHT, MID and FIND
functions.

Personally, I would write a VBA macro which parses each string, but you would
still have to know the original format (eg, lastname, firstname middlename etc.)

--
Regards,
Fred


"Jexcel" wrote in message
oups.com...
Hi,


I have a number of different spreadsheets that I need to impirt into a
single database. Unfortunately some of the information is in different
formats.


Would anyone be able to provide me with a solution that can turn:


A B Brown
ABC Brown
*A B C*Brown*
Brown, A B
Brown, Anthony Bernard
Brown Anthony Bernard
Mr A B Bernard


Into separate fields for title, forename, initials & surname


On a similar theme I also need to do much the same thing with
addresses:


1 Victoria Road, Victoria, Victoriashire, VC1 1VC


etc


And lastly (for now anyway) I also have some dates that I need to
convert from:


01012004 (dd/mm/yyyy)
112004 (d/m/yyyy)
20040101 (yyyy/mm/dd)
200411 (yyyy/m/d)


to 01/01/2004 (dd/mm/yyyy)


Hope someone can help


cheers