How to nest IF, MID & FIND formulas?

[Kelli Webb]

Hi, I would like to next the following formula to return the same result IF the MID starts with a 4, 7, 5 or 1

The attached is working for 40345, as you can see but I need it to return the same result regardless of the prefix of the cost centre.

 

[NARAYANK991]

Hi ,

My first reaction is why you have to look for the digit 4 ; can you not look for the text string NA/ , and take the 5 digits which follow ? Or even simpler , just take the 5 digits starting from the 12th position ? Or will the text string and / or the length of the text string before the digits vary ?

Narayan

 

[rahulshewale1]

Hii @Kelli Webb,

Please find the below mention formula ..also sheet attached
Formula 1 =MID(A2,FIND("/",A2,FIND("/",A2,FIND("/",A2)+1)+1)+1,5)
Formula 2 =MID(A2,FIND("NA",A2)+3,5)

Regard
Rahul Shewale..

 

[Naresh Yadav]

Hi,

Pls check attached file

regards
Naresh

 

[Kelli Webb]

Thank you! I can't wait to get back to work to try these out. Narayan, yes the NA field could be between 2 & 4 chars. I'll keep you posted

 

[Kelli Webb]

A Hugh thank you to both Rahul Shewale.. & Naresh, all 3 formula's provided are working perfectly - I can't thank you enough! Your assistance is greatly appreciated!
Kelli