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
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
ASKER
Thanks alot,
I know nothing of VB.. Is it possible for you to point me to some examples??
I know nothing of VB.. Is it possible for you to point me to some examples??
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
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
Good news ... I just realized I have a txt file with loads of examples ... all from EE
aaQicPostSplitFunctionExamples.txt
aaQicPostSplitFunctionExamples.txt
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
?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
ASKER
Thanks good job...Checking them out now..
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
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