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

 abc.value = right(abc.value,len(abc.value)-1

.

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