Help with nested IF for distinguishing between 4 and 5 digit numbers in a cell

Jay1

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.

John Jonassen

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))