Name, Date and Address Formats

[Jexcel]

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

 

[Guest]

I can perhaps help with 2 out of 3:

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

....> Into separate fields for title, forename, initials & surnameThis requires programming, which means you need to be able to write down a
set of rules to deal with each possibility...which may be hard!

1 Victoria Road, Victoria, Victoriashire, VC1 1VC

If this is your standard format, put all your data into a text file, rename
the extension to ".csv" and open it in Excel.

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

Again, put them into a text file, this time with a .txt extension, then open
it in excel and let the import wizard work on it. The third stage lets you
define a column as "Date". You'll have to fiddle about, and probably
separate the different formats out into groups. Good luck!

Nigel

 

[David McRitchie]

this one comes fairly close for the first part of question with names
http://www.cpearson.com/excel/FirstLast.htm

The address looks simple if you have only one address line before
the town, use Data, text to columns, delimited, comma

The date & time can perhaps be inputed with
=datevalue(a1) format as date
Your regional format for time must be same as the text representation