replace ' in cells

[Suz771]

I have a spreadsheet where all the fields (except those with numbers) have a
' before the data in the cell. The ' does not show up in the cell, but only
in the formula line. CTRL+H does not work, nor does it work using the ~ with
the ' (either before or after the ' character).

I need to remove this character in order for my formulas to work. The
spreadsheet has over 10K rows and 170 columns.

Thank you,
Susie

 

[Don Guillett]

1.. In an empty cell on the worksheet, type the value 1.
2.. Select the cell that contains the value 1, and click Copy on the Edit
menu.
3.. Select the cells that contain the text that you want to replace, and
then click Paste Special on the Edit menu.
4.. Under Paste, click Values. Under Operation, click Multiply, and then
click OK.

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[מיכ×ל (מיקי) ×בידן]

Did you try to press ONLY ONCE the key combination CTRL and ~ ?
[try to press and HOLD the CTRL key and then press on the ~ key [located at
the far left of the horizontal numeric keys].
Micky

 

[Gord Dibben]

If you change the alignment to center or right do the ' change to ^ or "

Turn off Transition Options.

What type of formulas are you using that care about the ' in cells without
numbers?


Gord Dibben MS Excel MVP

 

[ozgrid.com]

Use this code;

Sub RemoveIt()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
.Value = .Value
End With
End Sub