I made this thread as unsolved as I need a quick modification to the solution which Romulo had kindly provided me with. Just to clarify my pervious post I want to split the Rxxxxx Sales Manager in the attached spreadsheet so Col B has text "Sales Manager" and Col would have the Rxxxx bit. I can do this by using the formula which splits the text but when I run the code above the Sales Manager text from Col B disappeared. Basically I want to leave Col B intact.

Thanks again in advance