Remove lead single quote keeping lead zero

Hi All,

I need a VB code to remove all leading single quotes from all cells in a worksheet.
The cells may or may not contain quotes. Some of the cells contains alpha numeric,dates,numeric with leading zeros. i want to retain those zeros as well.
ex: '01234 as 1234
'qwerty12345 as qwerty12345
'02/12/2015 as 02/12/2015

I tried by using excel formula "SUBSTITUTE(cell reference,CHAR(39),"")". its working well but i need to do this for the entire worksheet where ever there is a leading single quote.
Any help is greatly appreciated.



Thanks
Shreeja

Hi pjwhitfield,

thanks for the reply.
the code shows syntax error on line

Please Login or Register  to view this content.

.

Thanks
Shreeja

Hey i dont know much about VBA but try this method--
1. Create a new sheet
2. in the developers tab go to insert>>module and paste this code
3. in the new sheet insert a button and right click >>>assign macro>>test
now click that button and see if your problem is solved.....


Sub test()
Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(Sheet1!RC,""'"","""")"
'replace sheet1 by your sheet which contains the data

Range("A1").Select
Selection.Copy
Range("A1:P22").Select
'here change the range to your data range

ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
'replace sheet1 by your sheet which contains the data

Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Hope it helps....
Regards

Hi Sourabhg98, it works well.. thanks a lot.. you saved me a lot of time.

Thanks
Shreeja.

Glad to know that it worked.....
Cheers