Filtering/Trimming Data In Fields

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
LVL 11
Eaddy BarnesITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
Eaddy BarnesITAuthor 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 / Systems AnalystCommented:
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
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 / Systems AnalystCommented:
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
Eaddy BarnesITAuthor Commented:
Thanks good job...Checking them out now..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.