apostrophe

[Steve]

Hi all, I just received an excel workbook where all the numbers have
been typed with an apostrophe ' in front of them, therefore no
formulas work, i.e., the worksheet does not view them as numbers.
Going in to each cell and removing the apostrophe works, but the table
is way to big to do that for every cell. I tried find/replace, but it
doesn't find the apostrophe's. I also tried reformatting the columns,
copying and using the various forms of "paste special", etc., and have
yet to find anything that works.

Any ideas?

Thanks,

Steve

 

[L. Howard Kittle]

Hi Steve,

=A1*1 will revert the text to a number, but may not work in your case if you
can't enter and pull down to do the conversion.

Also, if the numbers are over 15 characters the 16th on will be zeros.

HTH
Regards,
Howard

 

[Pete_UK]

Steve,

you can get rid of the apostrophe and convert the number by entering
the following into a suitable helper column:

=VALUE(RIGHT(A1,LEN(A1)-1))

assuming your data starts in A1. Copy this down the column for as many
rows as you have data. Then fix all these values using <copy> Edit |
Paste Special | Values | OK and <esc> or <enter>. You could then
copy/paste the values in the helper column to overwrite your values in
column A, then delete the helper column.

Hope this helps.

Pete

 

[Bob Umlas]

copy a blank cell, paste special both Values and Add onto the "problem"
range.

 

[Guest]

This worked in my cursory testing:

Insert a new sheet in your workbook

Select a problem sheet
Select the used-range of that sheet
Edit>Copy
Select the new sheet
Edit>Paste Special>Values
(That should eliminate the apostrophes)

Enter a 1 in a blank cell
Edit>Copy
Edit>Go To>
Click the [Special] button
Check: Constants
Click the [OK] button
(That should select all active cells in the sheet)
Edit>Paste Special>
Check: Values
Check: Multiply
Click the [OK] button

That should turn all of the text-numbers into numeric values
(The formulas will still be text, though)

Edit>Replace
Find what: =
Replace with: =
Click the [Replace All] button

That will clear up the formula issues.

As a final clean up...you might be able to select the entire source sheet,
then Copy/Paste Special>Formats to the new sheet.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Steve]

Hi all, thanks for the help. Everyone's solutions seem to work. From
an efficiency point of view, it seems that Bob Ulmas' solution was
best. Also, I could do this over ALL CELLS (even text), and it had no
effect on those for which no change was desired/required, so I could
do this in essentially one step to a whole sheet. Thanks again,
Steve

 

[Ragdyer]

This is the easiest way!
Simply open and close TTC.

Select the column of cells with the apostrophe, then:

<Data> <TextToColumns> <Finish>

And you're done.

 

[Guest]

Nice solution, RD. It even handles the case where formulas have a leading
apostrophe (eg '=SUM(A1:C1) )

***********
Regards,
Ron

XL2002, WinXP-Pro