Move the rightmost "filled" col in each row to Col

[clock-smith]

In the past this group has saved me from problems many times! here is another.

I have a worksheet formatted as General text. In each row, starting from col E the cells are filled from the left with no gaps but the number of cells filled may vary from 1 to about 50.

In each row I wish to copy the rightmost cell which is filled (not empty) to col B. If there is only one entry in a row (col F is empty) then col B should remain blank.

It looks like this. I have shown the required entry in col B

.... B....... E
1.. MN .... AB EF LM PP ZZ MN
2.. ZA .... AC PQ AT ZA
3.. ......... AD

 

[just_jon]

=IF(COUNTA(E1:IV1)<2,"",INDEX(E1:IV1,0,MATCH(REPT("z",255),E1:IV1)))

 

[clock-smith]

Thanks just_jon , but I still have a problem!

I am almost 80 and the brain is not as active as I would wish <grin>

In asking my question I wanted to keep the example simple so used alpha characters. In my actual worksheet the characters are numbers, 70, 75, 65 etc, but entered in"general format cells" for reasons which seemed valid to me when I built the worksheet.

Could you vary your formula for me please, so that it works with any alpha or numeric characters in the cells.

Sorry to be a nuisance and I apologise for leading you astray.

 

[just_jon]

Re: Move the rightmost "filled" col in each row to

Thanks just_jon , but I still have a problem!

I am almost 80 and the brain is not as active as I would wish <grin>

In asking my question I wanted to keep the example simple so used alpha characters. In my actual worksheet the characters are numbers, 70, 75, 65 etc, but entered in"general format cells" for reasons which seemed valid to me when I built the worksheet.

Could you vary your formula for me please, so that it works with any alpha or numeric characters in the cells.

Sorry to be a nuisance and I apologise for leading you astray.

No problem - to find end of data with numerics, we'll switch COUNTA to COUNT and the REPT function to a really big number.

=IF(COUNT(E1:IV1)<2,"",INDEX(E1:IV1,0,MATCH( 9.99999999999999E307,E1:IV1)))

Post back if you need more help.