Nested IF Function not working when the first condition is False

[deja2000]

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",A 8),"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.

[jonh]

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]

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]

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]

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]

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,L EN(A8)-FIND("_",A8,FIND("Inbound",A8)+8)),LEN(RIGHT(A8,LEN(A8)-FIND("_",A8,FIND("Inbound",A8)+8)))-4))

[Teeroy]

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("b ound",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)))))