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
C:\Sales\Fruit\Apple
C:\Sales\Fruit\Orange
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Right(Path,len(path) - Instr(Path,"Fruit\") - len("Fruit\")+1) as RightOfFruit
or
Right(Path,len(path) - Instr(Path,"Fruit\") - 5) as RightOfFruit
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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\R aspberry - my solution will return:
CaneFruit\Raspberry
Capricorn1 solution will return:
Raspberry
CaneFruit\Raspberry
Capricorn1 solution will return:
Raspberry
ASKER
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.
ASKER
There could be - I accepted your solution as well.
If you want to see the subfolders, your 'accepted' solution will not do that.
Open in new window