REPEATING COLUMN VALUE IN ROWS UNTIL REACHING A NEW VALUE

[NTBB]

I HAVE A VALUE IN A COLUMN WHICH IS MUCH LIKE A HEADING FOR THE FOLLOWING ROWS. I WOULD LIKE TO KNOW IF THERE IS A WAY TO REPEAT THE VALUE ONTO EACH OF THE ROWS IN THAT COLUMN - STOP WHEN IT COMES TO A NEW VALUE AND REPEAT THE NEW VALUE FOR THE FOLLOWING ROWS, STOP WHEN IT COMES TO ANOTHER VALUE, REPEAT THIS NEW VALUE FOR THE FOLLOWING ROWS AND SO ON. ie

CURRENT DESIRED RESULTS

1 CASH 1 CASH

WEST 1 WEST

EAST 1 EAST

SOUTH 1 SOUTH

2 A/R 2 A/R

JANE 2 JANE

FRED 2 FRED

SALLY 2 SALLY

 

[Hui]

NTTBB

Assuming your data is in A1:A8

B1: =+IF(ISNUMBER(VALUE(LEFT(A1,FIND(" ",A1)-1))),A1,"")

B2: =+IF(ISNUMBER(VALUE(LEFT(A2,FIND(" ",A2)-1))),A2,LEFT(B1,FIND(" ",B1))&A2)

Copy B2 Down


ps: Try and remember to use your Caps Lock key