splitting a field

  • Thread starter Thread starter Jean-Paul
  • Start date Start date
J

Jean-Paul

Hi,
Currently I have a field called "Street"
It contains data like: Spoorweglaan 7 or Grote Baan 125 bus 15
I created an extra field called "Number"
What I would like is to have a query that can split my initial field
into 2 fields so I finally will get:

Street: Spoorweglaan Number: 7
Street: Grote Baan Number: 125 bus 15

Can this be done and how?
Thanks
 
Jean-Paul

Are you saying that the instructions you'd give an assistant are to start at
the left, continue until you locate the first digit, then call everything to
the left the "street" and everything to the right, the "street number"
(NOTE: "Number" is a reserved word in Access, don't use it as a name for a
field)?

Are you quite confident that none of your data is like: Grote Baan Seven
bus 15?

Take a look at the Left(), Right() and Instr() functions...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Besides Jeff's warning about spelled-put numbers, you also need to take into
account numeric street names, e.g. 23 Str. 5 bus 15
-TedMi
 
Back
Top