Try this:

Select "Data" then "Text to Columns" then "Fixed Width." The next page will
allow you to break up the text so that the space and ' will appear in one
cell and the 123456 will appear in another cell. Once you've put the line in
you'll reach a page which will show "destination." Once there you should
pick the cell next to the cell you're trying to change otherwise it will
write over the original cell.

"gh" wrote:

I have a column that has values in it, space'123456. Is there a paste
special I can use to remove the space and ' , then copy the 123456 to
another cell? I can use a custom format to put in the spaces?

TIA