Need a formula to return the value of the next occupied cell in a row.

Hi guys.

New to this forum so I'm hoping I'm following the rules.

Anyway, the excel issue I'm trying to solve.

Column B contains data in some cells but not all. Cells are either blank or occupied with account numbers.

So:

B1 is blank. The next occupied row in B is B3 for example.

I need excel to basically occupy B1 and B2 with B3.

The problem is that the distance between the occupied is not set.

For example, I may need B4,B5,B6,B7,B8 and B9 to all be occupied with the next occupied cell in B (for example, B10) and return that value.

In summary, I need all blank cells in B to find the next occupied cell below itself and return that value. (To a neighboring cell if this will make in any easier) so if B1 is blank, find the below occupied and return it to C1 for example.

Any ideas?

Thanks in advance!