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

#### clock-smith

##### New Member
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

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
=IF(COUNTA(E1:IV1)<2,"",INDEX(E1:IV1,0,MATCH(REPT("z",255),E1:IV1)))

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.

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

clock-smith said:
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.

Replies
9
Views
708
Replies
2
Views
444
Replies
1
Views
396
Replies
0
Views
371
Replies
2
Views
246

1,203,502
Messages
6,055,770
Members
444,822
Latest member
Hombre

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back