Forum Discussion
Solved
IFS function #N/A
Hi all, I have built a formula (below) to help organise postcodes into patches, my formula shows a true value when I walk through it but returns #N/A. I have tested this across a number of v...
Shane10326 I created a table with the patch groupings from your formula. I expect if you make sure the table remains sorted on the first column, the formulas in E3 and F3 return the correct code and patch number from the postal code.
Yes 3 different rules JKPieterse
Which 3 rules?
- LEFT(E2,2)
LEFT(E2,3)
LEFT(E2,4)
The ,3 and the ,4 clash and i have tried removing ,3 all together and then using * or ? or "" but the formula still doesnt work as i need it to.- I guess I didn't make myself clear. Can you come up with a general rule as to when we need to use 2, 3 or 4 characters from the code?
- begins with SO, PO, DT, BH first 2 characters
these would return 5 6 or 7
beggins with RG*, SL*, SP* first 3 characters ( letters followed by numbers 0 through to 9 then a blank space )
these would return 1 2 3 5 or 6
begins with RG**, HP**, GU**, first 4 characters ( letters followed by numbers 11 through to 50 )
these would return 1 2 3 4 or 7
The only general rule i can come up with is always looking at the first 4 characters, however the last character will be a blank space a lot of the time.