Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can i remove the several hyperlink option after entering the . | Excel Discussion (Misc queries) | |||
remove characters from a text under a condition | Excel Discussion (Misc queries) | |||
Remove all spaces in a cell... | Excel Worksheet Functions | |||
Remove Hyperlink | Setting up and Configuration of Excel | |||
Remove single text characters | Excel Discussion (Misc queries) |