Link to home
Start Free TrialLog in
Avatar of error_prone
error_prone

asked on

String Search

Is there a way, (query preferred over VBA), that would allow me to extract certain lengths of characters immediately following a set of specific characters?  For example, if I have a field that stores directory names like below, how would I grab everything after "Fruit\"?

C:\Sales\Fruit\Apple
C:\Sales\Fruit\Orange
SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In a query the field would be:
Expr1: Left([FieldName], (InStr([FieldName], "Fruit\"))+1)

Open in new window

Ahh the age old choice of left or right :)
try

Instr(Path,"Fruit\"), Right(Path,len(path) - Instr(Path,"Fruit\") - len("Fruit\")+1) as RightOfFruit
Hi Dan:  <everthing after Fruit\> says everything to the Right - and don't get started on my age!
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I mean just second part :)

Right(Path,len(path) - Instr(Path,"Fruit\") - len("Fruit\")+1) as RightOfFruit

or

Right(Path,len(path) - Instr(Path,"Fruit\") - 5) as RightOfFruit
Avatar of error_prone
error_prone

ASKER

Capricorn, can you tell me how yours works?  I didn't even have to bother with the word fruit?  Why is that?  What's a general rule of thumb on using these formulas if trying to retrieve a length of characters preceding or following another set of characters?  Trying to learn this so I don't have to keep posting questions...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I really like Capricorns way of doing it, never mind the fact that mine doesnt work :)

Basically error_prone how it works is that rather than looking for the required characters from the start of the string it starts at the end so it looks for the first \ reading right to left or the last \ left to right.
So it returns the position of \ say 10. Then in the mid function

It effectively says Mid([path],10) Which means start at position 10 in [path] and show all the remaining letters.
Damn I obviously type way too slow :)
From  -  C:\Sales\Fruit\CaneFruit\Raspberry  - my solution will return:

CaneFruit\Raspberry

Capricorn1 solution will return:

Raspberry
Thanks so much!
error_prone:  So you are saying there are no folders after 'Fruit\' in your data - as I pointed out in my last post.
There could be - I accepted your solution as well.
If you want to see the subfolders, your 'accepted' solution will not do that.