Strip out the ' at the beginning of cell contents

C

Colin Hayes

Hi

All the entries to my cells in column A seem to have a ' symbol at the
start of them.

A formula I'm using in column B is failing because of this. When I
manually remove the ' from the formula box , my formula works.

Does anyone know how to strip out the leading ' from all the cells , and
leave all the real data intact?

Thanks.
 
M

Mike H

One way,

Put a zero in a cell and copy it.
select your data with apostrophes and then
Edit|Pastespecial
Select add
Click OK

Mike
 
R

Rick Rothstein \(MVP - VB\)

Are these entries all numbers? If so, select them, and then click on the
Data/Text To Column item in Excel's menu bar, select Delimited and then
click the Finish button.

Rick
 
C

Colin Hayes

Mike H said:
One way,

Put a zero in a cell and copy it.
select your data with apostrophes and then
Edit|Pastespecial
Select add
Click OK

Mike
Click to expand...

Hi Rick and Mike

Thanks for those solutions - both worked really well. I'm grateful.

The 'Add 0' solution worked on all entries , and puts a zero in blank
cells. I cleared these out using a simple search and replace.

The text to column solution works on the cells containing exclusively
numbers , but not on those containing some letters - of which there were
a few. I wonder if there is a similar route which would work on all
entries.

Anyway , job done - thanks again.

^_^
 
Top