complex if statements excel

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

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

Complex IF statements in Excel

resolvedResolved · 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.


 

Excel tip:

New Normal Worksheet

Do you want all your worksheets to confirm to a certain look? Then change the Defaults!!!
1. Press Shift+F11 to create a new worksheet
2. Press Ctrl+A to select (higlight) all cells, Press Ctrl+1, make any formatting changes then click OK.
3. Press F12 (Function 12 key) click in the Save As Type, drop down, then select Template (*.xlt)
4. Click in the Save in drop-down, then find the folder; c:_program files_microsoft office_office_start. (For the underscores shown use backslash)
Name your templete sheet.xlt, then press Enter.
Sheet.xlt is used when you insert a new worksheet (Shift+F11)

Note: These changes are permanent changes on your PC.

View all Excel hints and tips


Server loaded in 0.09 secs.