Get rid of apostrophes

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
Hi,

I have a column of data which is column B. What would be the code to get rid of all the apostrophes at the beginning of each cell from cell b1-b65536.

Cheers

Jay
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Why not just do a Find & Replace [Ctrl+H], with a Find What equal to ', and a blank value in the replace field?
 

jaypatel

Active Member
Joined
Nov 25, 2002
Messages
385
How about via a macro... as I would like this process automated.

Cheers

Jay
 

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831
TommyGun's answer is good enough but if a macro it must be then try

Code:
'The following procedure removes the apostrophe in front of text, values, or formulas in the current selection:
'Removes hidden apostrophes that are first characters.
'Works on cells with formulas, text, or values.
Sub ApostropheRemove()
    For Each currentcell In Selection
        If currentcell.HasFormula = False Then
            'Verifies that procedure does not change the
            'cell with the active formula so that it contains
            'only the value.
            currentcell.formula = currentcell.Value
        End If
    Next
End Sub

HTH
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,100
Messages
5,768,095
Members
425,452
Latest member
htay44

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