Filtering/Trimming Data In Fields

Eaddy Barnes
Eaddy Barnes used Ask the Experts™
on
Hey Dudes,

I have a file that I would like to import into access, now in that file there's a field called "Category Path". In this field there's a varying amount of information and this changes from row to row so it might be abit tedious.

What I would like to know is how would i got about trimming some of the data away leaving the information that I can use. Some of the information is before and after the data that i need.

I am thinking i may need to run that script/query and create a whole new table from it to get what I want.

See examples.

e.g

(Before) :Component:DRAM Memory:Desktop DRAM:DDR3 (240 Pin):DDR3 1066:
(After) "DRAM Memory"

(Before) :Component:Solid State Drive:SATA3:
(After) Solid State Drives

(Before) :Component:USB Drive:2.0 USB Drive:32GB:
(After) USB Drive

(Before) :Electronics:Accessory:
(After) Electronics

(Before) :PC & Laptop:Accessory:Battery & Charger:
(After) PC & Laptop

(Before) :Hardware & Software:Server & Workstation:Workstation Barebone:
(After) Hardware & Software

Is this even possible in Access??
Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Your best hope for this would be the Split() function which can be used in VBA or a query.
You would use the colon (:) as the delimiter.
However, it does not appear there is a lot of consistency in this field  relative to colon position ... like in these two examples:

(Before) :Component:USB Drive:2.0 USB Drive:32GB:
(After) USB Drive

(Before) :Electronics:Accessory:
(After) Electronics

Author

Commented:
Thanks alot,

I know nothing of VB.. Is it possible for you to point me to some examples??
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Here is the Microsoft bit on it (minimal)

https://msdn.microsoft.com/en-us/library/office/gg278528(v=office.15).aspx

Were not really allowed to post stuff from competing sites.  But if you Google Split Function Examples you will find them.
Note that Excel (vba) examples would apply as well.

But ... with no background in VBA this might be a challenge
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Good news ... I just realized I have a txt file with loads of examples ... all from EE
aaQicPostSplitFunctionExamples.txt
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Here are examples relevant to your data ...

?split(":PC & Laptop:Accessory:Battery & Charger:",":")(1)
PC & Laptop

?split(":PC & Laptop:Accessory:Battery & Charger:",":")(2)
Accessory

?split(":PC & Laptop:Accessory:Battery & Charger:",":")(3)
Battery & Charger

Keep in mind that Split is basically an Array() .... so where I have (N) on the end ... it just days return Element N

Author

Commented:
Thanks good job...Checking them out now..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial