Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing dots with commas using macro
Hi, I have a problem and maybe somebody can give me some good advice. The problem is that I need to replace delimiter dots with commas, otherwise numbers will be handeled as text. When I simply use Replace function from Edit menu, it work perfectly - dots will be replaced with commas and text will be converted to numbers. I tried to record this function as a macro, but it doesn't work in macros. It replaces dots with commas, but text won't be converted to numbers and I can't use them in functions as SUM etc. Its strange because it works when done manually but doesn't work in recorded macro. How can I fix that? Thank you! -- maxest ------------------------------------------------------------------------ maxest's Profile: http://www.excelforum.com/member.php...o&userid=30143 View this thread: http://www.excelforum.com/showthread...hreadid=498282 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing dots with commas using macro
It sounds like you have the comma switch taken care of. The following
changes the specified range to number format: Sub ConvertNum() Range("A1:A10").Select Selection.NumberFormat = "0.00" End Sub hope that helps "maxest" wrote: Hi, I have a problem and maybe somebody can give me some good advice. The problem is that I need to replace delimiter dots with commas, otherwise numbers will be handeled as text. When I simply use Replace function from Edit menu, it work perfectly - dots will be replaced with commas and text will be converted to numbers. I tried to record this function as a macro, but it doesn't work in macros. It replaces dots with commas, but text won't be converted to numbers and I can't use them in functions as SUM etc. Its strange because it works when done manually but doesn't work in recorded macro. How can I fix that? Thank you! -- maxest ------------------------------------------------------------------------ maxest's Profile: http://www.excelforum.com/member.php...o&userid=30143 View this thread: http://www.excelforum.com/showthread...hreadid=498282 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing dots with commas using macro
No, it didnt help. I'll try to explain that situation better. I'm using commas as decimal separator, but data that I work with, come from another application (web-based) that uses dots as separator. When paste data tables to a Excel chart all numbers are handeled as text. M objective is to convert them to the numbers. After using Replace function in macro it replaces dots with commas, bu numres are still text and can't be ussed in financial functions. I helps when I manually edit cell (F2) and hit Enter after that, but cant edit all 500 cells manually. When I use Replace function from Edit menu manually, it work perfectl and I dont have problem with number that are in text format. I have tried also to change format of cell in every way but it doesn work. Work only F and Enter -- maxes ----------------------------------------------------------------------- maxest's Profile: http://www.excelforum.com/member.php...fo&userid=3014 View this thread: http://www.excelforum.com/showthread.php?threadid=49828 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Replacing dots with commas using macro
Sorry about that, I got the commas and dots switched around. The number
format you are looking to use is european style (well pretty much anywhere other than the U.S. and canada use it). In order to have a number (0.00) be formatted like this 0,00 and be used as a number in excel you've got to change your global settings. Add this into your code: 'You may want to put this in the workbook_open event of the workbook 'That way you don't have to think about it. With Application .DecimalSeparator = "," .ThousandsSeparator = "." .UseSystemSeparators = False End With 'Now you can use the following. 'Don't worry about the dot because the globals are changed Range("A1:A10").Select Selection.NumberFormat = "0.00" 'You will also want to reset your globals. 'Possibly on a workbook_beforeclose event "maxest" wrote: No, it didnt help. I'll try to explain that situation better. I'm using commas as decimal separator, but data that I work with, comes from another application (web-based) that uses dots as separator. When I paste data tables to a Excel chart all numbers are handeled as text. My objective is to convert them to the numbers. After using Replace function in macro it replaces dots with commas, but numres are still text and can't be ussed in financial functions. It helps when I manually edit cell (F2) and hit Enter after that, but i cant edit all 500 cells manually. When I use Replace function from Edit menu manually, it work perfectly and I dont have problem with number that are in text format. I have tried also to change format of cell in every way but it doesnt work. Work only F and Enter. -- maxest ------------------------------------------------------------------------ maxest's Profile: http://www.excelforum.com/member.php...o&userid=30143 View this thread: http://www.excelforum.com/showthread...hreadid=498282 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combobox changes the decimal commas to dots - How to prevent it? | Excel Discussion (Misc queries) | |||
Change dots to commas!! | Excel Discussion (Misc queries) | |||
Replacing commas with carriage return | Excel Worksheet Functions | |||
Change Dots into Commas as Decimal Divider | Excel Discussion (Misc queries) | |||
Replacing Tab with commas | Excel Programming |