replace ' in cells
 

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

replace ' in cells
 

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

"Suz771" wrote in message
...
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

replace ' in cells
 

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


"Suz771" wrote:

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

replace ' in cells
 

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

On Fri, 2 Apr 2010 07:33:02 -0700, Suz771
wrote:

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

replace ' in cells
 

Use this code;

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



--
Regards
Dave Hawley
www.ozgrid.com
"Suz771" wrote in message
...
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