Break Up a Text String in Microsoft Access
Using the InStr Function and String Manipulation
Q: I have an Excel spreadsheet where a single
column can sometimes have several different types of data in it. I need
to be able to separate those pieces of data out into different fields.
How can I do it?
A: Here is the sample data
that the customer sent me:
As you can see, the Price field contains all kinds of different
bits of data... sometimes a single price like $450,000, sometimes a
price per square foot, sometimes a range of prices, and sometimes just
"See Agent". We need to be able to break all of these down - and even
separate out the minimum and maximum values for those items that give
the range of prices so the data looks like this:
How do I do this? Well, the key is to know all of your String
Manipulation Functions and to use them properly. In this lesson I needed
to use all of these functions:
In addition to:
Here is a video tutorial that shows everything I did, step-by-step, and the
sample database that contains all of the query code:
Now, this video goes at a pretty fast clip. It's designed for people who have
already taken all of the classes listed above (at least through
Access 308).
If you have not, then I suggest you take those classes first, and then try to
tackle this one!
| 
