98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Complex IF statements in Excel
Complex IF statements in Excel
Resolved · Urgent Priority · Version 2010
Nick has attended:
Excel Intermediate course
Complex IF statements in Excel
I have been given a spreadsheet with an IF statement which I am trying to ammend.
The current if statement is:
=IF(ISERR(FIND("tag=",F3)),"-",MID(F3,FIND("tag=",F3)+4,7))
This is referiing to the following hyperlink:
http://mkge.englishtown.com/online/lp/PrivateTeacher_CRM043.aspx?lng=ar& ;etag=E112546&first_name=%%First Name%%&last_name=%%Last Name%%&email=%%Email%%
And is basically trying to extract the E112546 out.
This currently works as the IF statement is based on a fixed width of 7.
I have just been told that the hyperlink is now changing and that the want to use freeform text which could be longer then 7 characters and up to 25 characters.
EXAMPLE 2:
http://mkge.englishtown.com/online/lp/OnlineStudent_CRM043.aspx?lng=pt& ;ctr=br&etag=mkgepm_303_gre_oo_lpgr&first_name=%%First Name%%&last_name=%%Last Name%%&email=%%Email%%
So the code in the above example would be:
mkgepm_303_gre_oo_lpgr
So we basically need a statement which captures anything after the "etag=" but before the "&first_name="
Now to make matters even worse, I have heard that the "&first_name=" will not always appear after the code.
Sometimes there will be nothing, and other times there could be:
"&ptn="
"&email="
I think there is basically 4 combinations.
What I urgently need to know, is, Can a comprehesive IF statement to capture any size code (up to 25 characters) be written?
If you can help with this then that would be great.
Kind regards
Nick
RE: Complex IF statements in Excel
Hi Nick
Thanks for getting in touch. This was a very interesting IF statement to look at.
To make this dynamic, instead of returning a fixed width of 7, we start searching from tag= until we find the next & character.
If you subtract this position from where tag= finished, this is how long the 'etag' is.
=IF(ISERR(FIND("tag=",F3)),"-",MID(F3,FIND("tag=",F3)+4,FIND("&",F3,FIND("tag=",F3))-FIND("tag=",F3)-4))
I hope this works for you. Have a good look at the formula and try it out. If you need further explanation then please let me know.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Complex IF statements in Excel
Hi Gary
Thanks for this I will try it out. I wasn't aware that you can do it until the next "&".
Also, what would happen in the case where there is nothing after the etag?
EG
http://mkge.englishtown.com/online/lp/OnlineStudent_CRM043.aspx?lng=pt& ;ctr=br&etag=mkgepm_303_gre_oo_lpgr
Would the above formula still work?
Kind regards
Nick
RE: Complex IF statements in Excel
Hi Nick
No, that won't work! However, if we wrap the whole thing in a further IFERROR that extracts that if it is the last item:
=IFERROR(IF(ISERR(FIND("tag=",F3)),"-",MID(F3,FIND("tag=",F3)+4,FIND("&",F3,FIND("tag=",F3))-FIND("tag=",F3)-4)),MID(F3,FIND("tag=",F3)+4,LEN(F3)))
PS. this will only work in Excel version 2007 onwards.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 2 Apr 2013: Automatically marked as resolved.
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:New Normal WorksheetDo you want all your worksheets to confirm to a certain look? Then change the Defaults!!! |