CopyCellContents
 

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

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

In the cell beside it you can try entering =right(cell reference,6). This
should return only the 6 characters on the righthand side.

"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

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