Parsing Data From String of Text

[bbratcher]

Hello All,

Thank you for this forum as I have surely learned a lot here. I am seeking assistance parsing data from a string of text in Access 2010. Sample data and desired result below. I have provided a couple of samples to show varied text string. I am a novice with access. Any assistance you can lend would be greatly appreciated.

Sample Data:
200100 Vac tac mop interior 62.76 1 1 4.0 62.76 62.76
404103 Change bank 1 prefilters 31.38 1 1 2.0 31.38 31.38


Desired Result:

Column1 Column2 Column3 Column4 Column5 Column6 Column7
200100 62.76 1 1 4.0 62.76 62.76
404103 31.38 1 1 2.0 31.38 31.38

Thank you in advance for your assistance.

 

[bbratcher]

I see that my "desired result" may be confusing. In the desired result, the delimiter would be a "space", so the data after each space would reside in separate columns. Hopefully this makes sense. Thanks again!

 

[xenou]

Access has an import text feature on the external data tab. That's assuming you have your data in a text file. I like to have the table ready, so all the field names and data types are correct in the table before you import. But you have some control over all of that even if you are creating the table at the time of import. If you don't have field names and you haven't prepared a destination table, the fields probably get ugly names like "Field1", "Field2", "Field3" ... you get the picture.

Explanation:
Import Text File In Access 2010

Another example with YouTube video (part2 of the video for text import, Excel import is first):
https://www.youtube.com/watch?v=JWyTO1PJsh4

Edit:
note, do be careful that your file is really space delimited. Below the first line is space delimited with three fields, the second is space delimited with two fields.
AnnualRevenue 1000000USD
Annual Revenue 1000000USD

 

[Micron]

If you already have data looking like this in a field and cannot redo the import and control the parsing as suggested, I'd say there's no way. I spent a few moments thinking about combining the Instr and Mid functions to parse it based on the position of spaces, and then determining if the chunk represented a number. Then I realized you have overlooked a number in the desired results you posted. bank 1 prefilters contains spaces around a number, which you didn't include. So either method will fail - probably for a lot more than the one example you showed. I think you've got a bigger problem than what it first seems to be.