Help with nested IF for distinguishing between 4 and 5 digit numbers in a cell
Hello,
We have a formula in this sheet that currently distinguishes between multiple PO#s in the PO column and separates them into individual columns when there's more than 1. In anticipation of using 5 digit POs alongside the standard 4 digits we currently use, we're having difficulty setting up a something like a nested countif formula to check when a number has 5 digits or 4 digits, then parsing them accordingly.
We've attached a screenshot with the current formula along with the columns in use. For context the formula first checks if the PO# cell is blank and if so adds an "x" to the related PO column for use on another sheet. Any assistance is greatly appreciated.
Answers
-
Hi @Jay1 !
I think I have a solution for you, but it isn't a one formula and drag it across kind of thing, but here we go. Please make sure to copy and paste PO 1 as it, this is because the PO Numbers in your PO# Column have a return carriage, so we are mimicking it in the formulas. This will take care of it, no matter how many characters are in each PO number.
PO 1:
=IF(LEFT([PO#]@row, FIND("
", [PO#]@row) - 1) = "", "x", LEFT([PO#]@row, FIND("
", [PO#]@row) - 1))
PO 2:
=IF(MID($[PO#]@row, LEN([PO1]@row) + 2, 5) = "", "x", MID($[PO#]@row, LEN([PO1]@row) + 2, 5))
PO 3:
=IF(MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + 3, 5) = "", "x", MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + 3, 5))
PO 4:
=IF(MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + LEN([PO3]@row) + 4, 5) = "", "x", MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + LEN([PO3]@row) + 4, 5))
PO 5:
=IF(MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + LEN([PO3]@row) + LEN([PO4]@row) + 5, 5) = "", "x", MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + LEN([PO3]@row) + LEN([PO4]@row) + 5, 5))
PO 6:
=IF(MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + LEN([PO3]@row) + LEN([PO4]@row) + LEN([PO5]@row) + 6, 5) = "", "x", MID($[PO#]@row, LEN([PO1]@row) + LEN([PO2]@row) + LEN([PO3]@row) + LEN([PO4]@row) + LEN([PO5]@row) + 6, 5))
Help Article Resources
Categories
Check out the Formula Handbook template!
