Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

complex if statements excel

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Complex IF statements in Excel

Complex IF statements in Excel

ResolvedVersion 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.

Excel tip:

Quickly select a block of data

To quickly select a block of data make sure your active cell is somewhere whithin the block of data and then press Ctrl+* or Ctrl+Shift+8.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.