removing leading ' (apostrophe)

[rilo1970]

I have been given a spreadsheet by another department that was exported
form another package. The problem I have is that all the data in
every cell has a leading ' (apostrophe). For example, when you look at
the contents of the cell that displays as 12/31/2003, it shows
'12/31/2003. I've tried to format the cells, but that doesn't work.
Is there any easy way to remove the leading character for the whole
column or sheet? Thanks in advance for help.

 

[Don Guillett]

edit/replace

 

[rilo1970]

Don,
I tried the search and replace, it will not work. To clarify, the data
has a leading apostrophe, like '12/31/2003 (not visibible in the cell
unless you look at the cell contents itself). So, it displays as
12/31/2003, but when you highlight the field, the ' shows up.

 

[Peo Sjoblom]

Format the cells as date, copy an empty cell, select the dates and do
edit>paste special and select add, you i might have to format the dates
again if you get the serial number (# of days since jan 0 1900)

 

[David McRitchie]

with a macro you can reenter the value into xxx.formula
Reenter the cell current cell content (#reenter)
Rearranging Data in Columns
http://www.mvps.org/dmcritchie/excel/join.htm#reenter

Format the cells as date, copy an empty cell, select the dates and do
edit>paste special and select add, you i might have to format the dates
again if you get the serial number (# of days since jan 0 1900)

 

[2rrs]

You can use the Text to columns command.
select the column you want to change.
go to; data >text to columns >delimited

next >check other >enter '(apostrophe)
next >finish

I used this; it changed the col in place(without a helper col)
and cells were in date format