Formula to use in complicated replace

Hi guys, i just hope that someone would have an idea of which formula is best to use in this case...

I have a spreadsheet (the whole lot, just the top is here)


2059251 On order
Item code : 1006668HD


2059789 On order
2059867 On order
Item code : 1043628

2059866 On order
Item code : 1045841HD



basically what i need to do is: replace on order with the next value, so the final look should be:
2059251 1006668HD
Item code : 1006668HD


2059789 1043628
2059867 1043628
Item code : 1043628

2059866 1045841HD
Item code : 1045841HD


Any ideas on even which formula to use and how to specify it? Can not do it manually, as the report is huge...

Hi FDibbins,

Thank you

And thanks for the prompt reply

But look what happens for me:

Capture.jpg

Looking at your result i thought that after I've got the result that your screen shot shows, I'll just divide the result by space into two different columns, copy and paste and it's done
But it substitutes my cell with the one of the left, not down, and unfortunately I am not able to modify the formula because I can't get my head around what's what...
Sorry, can you please let me know if i do something wrong...

FDibbins, it doesn't do anything now... at all...

Capture.jpg

Sorry and this is the screenshot... maybe i do something wrong?

Thanks you very much! It worked!!! :D

Thanks a lot for your help!!!