Example: campaign=TEST&
I need the query to return "campaign=Test"
campaign=ExcelForum&
I need the query to return "campaign=ExcelFOrum"
I need everything left of the "&" , but there are multiple ampersands within the text string. I need a query that will locate "campaign=" then look for the first instance of an "&" after "campaign=", and then return everything prior to the "&".
Assuming your string is in L8:
=MID(L8,FIND("campaign=",L8),FIND("&",RIGHT(L8,LEN(L8)-FIND("campaign=",L8))))
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
Would I replace L8 with the actual table and field name if I want to do this in a query in MS Access?
I think Pauley thought this was an excel question, not an access question - that is an excel formula(and so did I actually, until I read your last post and then looked at the forum it was posted in)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Oh, yeah. Whoops.
However, I believe it is very similar. FIND becomes InStr. You would create a calculated field and the L8 would be replaced with your original field name (ex. [OrigString]). Something like this:
MID(L8,InStr([OrigString],"campaign="),InStr(RIGHT([OrigString],LEN([OrigString])-InStr([OrigString],"campaign=")),"&"))
I may be missing something from the way the question is worded, but I am assuming that I have a table in Access named Test with one field named ID. The table has various records such as "campaign=TEST&" and "campaign=Forum&".
What I did was to build a custom function in VBA as follows:
Then I run the following SQL statement:
Please Login or Register to view this content.
The results I get are as follows: "campaign=Test" and "campaign=Forum".Please Login or Register to view this content.
Last edited by Dimitrov; 02-13-2014 at 09:47 PM.
Dimitrov - the examples were simpler than the possibilities. The input string could have multiple &'s and other text both before and after "campaign=".
AlphaSkidz - Just checked my solution in a query table. It worked other than I left one more L8 in the equation. This is what worked for me:
MID([OrigString],InStr([OrigString],"campaign="),InStr(RIGHT([OrigString],LEN([OrigString])-InStr([OrigString],"campaign=")),"&"))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks