Remove single quote

[Avadivelan TCS]

Does anyone know how to escape(remove)single quote to
appear before a values, when a report is exported from
Access to Excel. I tried to use "replace", "clean"
or "right" functions but it does not help.
I am having more than 3600 columns like that:
‘394,3568,789
'7896,456,1234
Can you suggest a macro for this

Thanks in advance for your reply

 

[Avadivelan TCS]

Thanks Roger,
But a most of the single cell length is more than 1200.It contains more
numbers like: '789,895465,658,665,656,6532,36,65656,32569,65659656,...etc
so EXCEL says"FORMULA IS TOO LONG".

 

[Roger Govier]

Hi

Then maybe a small piece of VBA code like the following

Sub Replacecommas()
Dim c As Range, rng As Range, s As String
Set rng = Range("A1:A5000") <===== Change to suit requirement
For Each c In rng
s = c.Value
c = Replace(s, "'", "")
Next
End Sub

You can copy the code and paste it into your Visual Basic Editor
(VBE) in a Standard Module located in your file.
To do this,

Alt + F11 (open VBE)
Ctrl + R (open Project Explorer)
Select the file name on the left
Insert > Module
Paste code in Module

David McRitchie has lots of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[SteveW]

A solution I remember reading on here a while back.
in a seperate cell put a 1
Copy that cell
then Select the column of 'text-numbers'
Paste Special...
Choose Multiply

Steve