Removing Apostrophe from Cells

[raveille]

I'm trying convert cells with currency formatting that have an apostrophe before every number.

Here's an example of what the information in the cells look like: '$450.00

When I double-click a cell, the apostrophe goes away. But there are thousands of rows and I don't have the time or patience to double-click thousands of cells. Is there a way to double-click an entire column to get rid of the apostrophe? Or is there a way to format the cells so the apostrophe goes away on all of them?

A huge thanks in advance if anyone knows!

 

[Jonmo1]

Welcome to the board.

Try copying a blank cell
Then highlight the problematic column
Right Click - Paste Special - Values - Add


Hope that helps.

 

[antthorne]

Hi raveille

you could use a macro something like

Sub Test2() ' Select cell A2,
'Change A2 to be you first row of data
Range("A2").Select
Do Until IsEmpty(ActiveCell)
Activecell.activate
ActiveCell.Offset(1, 0).Select
Loop
End Sub

I haven't tested it but it should work

Thanks
Ant

 

[Rick Rothstein]

Hi raveille

you could use a macro something like

Sub Test2() ' Select cell A2,
'Change A2 to be you first row of data
Range("A2").Select
Do Until IsEmpty(ActiveCell)
Activecell.activate
ActiveCell.Offset(1, 0).Select
Loop
End Sub

I haven't tested it but it should work

If the OP wants a macro solution, and IF there are NO formulas on the worksheet, then this simpler macro could be used...

Sub Test3()
ActiveSheet.UsedRange.Value2 = ActiveSheet.UsedRange.Value2
End Sub
[code]

If there are formulas on the worksheet, but the non-formulas cells are confined to identifiable ranges, then this modified version of the above code can be used instead (where, of course, the non-formula ranges are assumed)...

[code]Sub Test4()
  With Range("A2:C100,F2:H100,M2:P100")
    .Value2 = .Value2
  End With
End Sub