Remove single quote
 

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

Remove single quote
 

Hi

Type Ctrl+A to select the whole sheet.
Ctrl+H to bring up Find and Replace
Find '
Replace (leave blank)
Replace All

Beware, that if any numbers are greater than 15 digits, then all numbers
after the 15th digit will be replaced by 0's

--
Regards

Roger Govier


"Avadivelan TCS" wrote in
message ...
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

Remove single quote
 

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,656596 56,...etc
so EXCEL says"FORMULA IS TOO LONG".

"Roger Govier" wrote:

Hi

Type Ctrl+A to select the whole sheet.
Ctrl+H to bring up Find and Replace
Find '
Replace (leave blank)
Replace All

Beware, that if any numbers are greater than 15 digits, then all numbers
after the 15th digit will be replaced by 0's

--
Regards

Roger Govier


"Avadivelan TCS" wrote in
message ...
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

Remove single quote
 

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



--
Regards

Roger Govier


"Avadivelan TCS" wrote in
message ...
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,656596 56,...etc
so EXCEL says"FORMULA IS TOO LONG".

"Roger Govier" wrote:

Hi

Type Ctrl+A to select the whole sheet.
Ctrl+H to bring up Find and Replace
Find '
Replace (leave blank)
Replace All

Beware, that if any numbers are greater than 15 digits, then all
numbers
after the 15th digit will be replaced by 0's

--
Regards

Roger Govier


"Avadivelan TCS" wrote in
message ...
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

Remove single quote
 

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

On Thu, 05 Oct 2006 08:44:02 +0100, Avadivelan TCS
wrote:

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




--
Steve (3)