Name, Date and Address Formats

  • Thread starter Thread starter Jexcel
  • Start date Start date
J

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
 
I can perhaps help with 2 out of 3:
A B Brown
ABC Brown
*A B C*Brown*
Brown, A B
Brown, Anthony Bernard
Click to expand...
....> 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
Click to expand...

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)
Click to expand...

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
 
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
 
Back
Top