Get the first numerical data if cell in column A is 10 characters long

[chronot]

Hello my friends!



I have this spreadsheet with two columns. For instance, in cell A13 I have the code 9704.00.00 (10 characters long) but the percentage is in cell B16, it's not in the same row as the code in column A. How can I move the value in cell B16 so it's located in B13 (the same row as the code).

This problem happens in certain parts of the spreadsheet. In other cases, for example A22, the percentage is in the same row.

I'll be waiting for your ideas. I have tried conditional formulas with AND and IF functions but I can't get the desired result.
Thank you very much for your help!

 

[Luke M]

In cell C6 of your example:
=IF(LEN(A6)<>10,"",IF(ISNUMBER(B6),B6,INDEX(B6:B$100,MATCH(TRUE,INDEX(ISNUMBER(B6:B$100),0),0))))

Copy formula down as needed.

 

[chronot]

Wonderful, Luke M, infinite thanks for your great help!!
Why do you use an INDEX function inside MATCH?

 

[Luke M]

INDEX is able to produce an array of values naturally, rather than a single output like most functions. I use the INDEX to produce a True/False (finding which cells have numbers) array from B6:B100, and then the MATCH function searchs this array for the first TRUE value to get the position of that TRUE. We then feed that into another INDEX, to get the actual value from the cell.

 

[chronot]

Thank you very much for your explanation Luke M!
I tried to use only ISNUMBER function within the MATCH function, MATCH(TRUE,ISNUMBER(B6:B$100),0))))
but in some cases I get #N/A. Could you explain me why?