PDA

View Full Version : Nested IF Function not working when the first condition is False



deja2000
10-18-2012, 07:50 AM
I have a spreadsheet that consist of lines of copied text strings from a Notepad file. The text strings are in column A and look like this, "D:\CallRecorder\Recordings\21955\2012-10-01\21955_2012_10_01_115748_Outbound_JasonKnight_888-575-5762.wav".

In Column B I have a MID function formula to pull out the date in the sting, "=MID(A8,34,10)". This works great, but I also need to pull out the associate’s name from the string, i.e. "JasonKnight". But the name of the caller falls differently in the string if it is an outbound call versus an inbound call, so I couldn't use the MID function.

In Column C I tried the following formula to pull out the names of the associates, "=IF(FIND("Jason",A8),"Jason",IF(FIND("James",A8),"James",IF(FIND("Katie",A8),"Katie",IF(FIND("Lindsay",A8),"Lindsay",IF(FIND("Bryce",A8),"Bryce","")))))".

The formula works if the string contains Jason's name, but I get the "#Value!" error if the name is any of the other associates.:banghead:

jonh
10-18-2012, 09:47 AM
Use find to check if it is inbound or outbound and use a separate version of your working mid function for inbound cases?

Iirc there's an iserror function which may be of use.

So your function might look something like ...

If (iserror(find("inbound")), use OUTBOUND , use INBOUND)

Sorry, I don't have access to excel at the moment.

deja2000
10-18-2012, 10:12 AM
Thanks for your quick response! I am not well versed in Excel, so I am a little confused by the response. How would I use the ISERROR function to pull out the different associates' names from the text strings? Should I add it to the MID function formula I used to pull out the date in column B?

jonh
10-18-2012, 12:01 PM
Oh sorry, I thought you had everything working for Outbound cases.

Well, assuming the filename formats are the same except for the word Outbound changing to Inbound this should work - except, like I said I don't have Excel at the moment so wrote it in Open Office. I think the functions work the same way though ...


=LEFT(MID(A1,IF(ISERROR(FIND("Outbound",A1)),FIND("Inbound_",A1) + LEN("Inbound_"),FIND("Outbound_",A1) + LEN("Outbound_")),99),FIND("_",MID(A1,IF(ISERROR(FIND("Outbound",A1)),FIND("Inbound_",A1) + LEN("Inbound_"),FIND("Outbound_",A1) + LEN("Outbound_")),99))-1)

deja2000
10-26-2012, 09:45 AM
I tried the formula you provided, but it returns a "value" error. The text strings look like this:

Outbound:
D:\CallRecorder\Recordings\21955\2012-10-01\21955_2012_10_01_115748_Outbound_JasonKnight_888-575-5762.wav

Inbound:
D:\CallRecorder\Recordings\21955\2012-10-02\21955_2012_10_02_092920_Inbound_207-553-7110_JasonKnight.wav

mohanvijay
10-26-2012, 02:53 PM
try this

=IF(ISERROR(FIND("Inbound",A8)),MID(A8,FIND("Outbound",A8)+9,LEN(A8)-((LEN(A8)-FIND("_",A8,FIND("Outbound",A8)+9))+FIND("Outbound",A8)+9)),LEFT(RIGHT(A8,LEN(A8)-FIND("_",A8,FIND("Inbound",A8)+8)),LEN(RIGHT(A8,LEN(A8)-FIND("_",A8,FIND("Inbound",A8)+8)))-4))

Teeroy
10-26-2012, 05:47 PM
I probably would have used VBA and splits for this but if you want to do it in the worksheet this might work

=IF(ISERROR(FIND("out",A1)),(MID(A1,FIND("bound",A1)+6,(FIND("_",A1,FIND("bound",A1)+6)-(FIND("bound",A1)+6)))),(MID(A1,(FIND("_",A1,(FIND("bound",A1)+6))+1),(FIND(".wav",A1)-(FIND("_",A1,(FIND("bound",A1)+6))+1)))))