I have some text that I've taken in from a pdf, however the columns are not lined up the way I need them to be:


I want to duplicate the column A and D values down into any empty spaces/rows. So in the end, all the spaces in those columns would become like this:


Is there any way automatically to do that to those columns in general?

An automatic way to delete the rows with blanks at the end would be really useful as well.

1 Answer 1


You can do this with a simple formula. Given the range shown in your example, insert a new column to the right of column A. In this new column B, write:

B136: =A136
B137: =if( isblank(A137) ; B136 ; A137 )

Duplicate the formula in B137 into the cells below it.

Let's unpack this:

  1. B136 looks to the left for its value (A136).
  2. B137 looks to the left for its value (A137), but if blank, uses the previous value (B136).
  3. B138 looks to the left for its value (A138), but if blank, uses the previous value (B137).
  4. … and so on.

Depending on your needs, you might be done at this point. Optionally, you can hide column A. Or you can copy column B, "paste values only" into column A, and delete column B.

You asked to do the identical operation with the data in column D, so the solution will be the same.

E136: =D136
E137: =if( isblank(D137) ; E136 ; D137 )

Please post your followup question about rows with blanks at the end as a separate question, and provide more detail. It's not clear what the problem is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.