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