| Tips & Tricks | |
|
| ||||||||||||||||||||||
Break Up a Text String in Microsoft AccessUsing 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?
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! ADDENDUM (7/24/09): Travis asks: How do I convert this date from 1090621 into 6/21/2009? Answer: You'll need to learn the string manipulation functions: left,
right, mid, from above. That last one should be what you're looking for.
ADDENDUM (7/25/09): This seems to be a popular topic lately. Here's another one. Scott says he has a table with values that look like this:
He needs to separate out that code - the [a] or [1] - from the rest of the name. Again, our string functions come to the rescue. Here's another video:
| ||||||||||||||||||||||
| ||||||||||||||||||||||
|
| ||||||||||||||||||||||
|
| ||||||||||||||||||||||
