if you want a number output, try in the cell you want the value
=VALUE(SUBSTITUTE(A1," '",""))
Do you want spaces? I don't understand the last line of your posting.

"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