Removing Apostrophe from Cells

raveille

New Member
Joined
Aug 15, 2013
Messages
1
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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

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


Hope that helps.
 
Upvote 0

antthorne

Board Regular
Joined
Feb 18, 2010
Messages
151
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
 
Upvote 0

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,136
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...

Code:
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
 
Upvote 0

Forum statistics

Threads
1,186,986
Messages
5,960,961
Members
438,504
Latest member
KristofGoossens

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top