Apostrophe problem

[bcap]

Hello,

I am working with a spreadhseet and the data looks normal, however
when you click on the cell the data value contains a leading
apostrophe.

for example the data looks like this:

'4
'8
'10
'8


IS there a quick and easy way to get rid of the leading " ' "? If
so, an advice on how to do this would be greatly appreciated!

 

[Guest]

Does "Find and Replace" not work? I would record a macro doing a manual
Replace and, if it works, you can modify that code to work in your macro.

 

[Guest]

I just discovered my suggestion does not work! However, you can add
something like
Sub testit()
With Cells

Set rng = .Range(.Cells(1, 12), .Cells(1, 12).End(xlDown))
rng.Select
End With

Dim RowNdx As Long
Dim ColNum As Integer
ColNum = Selection(1).Column
For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row Step -1
Cells(RowNdx, ColNum) = Mid(Cells(RowNdx, ColNum), 1, 10)
Next RowNdx--


Best wishes,

Jim

 

[Gary Keramidas]

don't know which version of excel you have, but in 2003 i can do it a couple of
ways.

tools/options error checking tab, check number stored as text. then click the
error indicator on the cell and choose convert to number.
or
if the numbers are in a column of contiguous rows, for example, select a like
region of blank cells, right click and choose copy, right click the top of the
range with the numbers stored as text and choose pastespecial. under operations
choose add and the cells should now be numbers.