Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma replacement
I am in Europe. I use by default coma as decimal separator.
In a particular situation I need to replace the coma by point. I have this code: Columns("p:p").Select Selection.NumberFormat = "General" Selection.Replace What:=",", Replacement:="." But nothing is happening. Column P still has comma as decimal separator. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma replacement
on the sheet that you are working on, select the column , or range of cells,
set the format to general. then go to 'Edit', 'Replace', put a ',' in the Find What and a '.' in the Replace With and then hit 'Replace All'. I think that should work. "Raul Sousa" wrote: I am in Europe. I use by default coma as decimal separator. In a particular situation I need to replace the coma by point. I have this code: Columns("p:p").Select Selection.NumberFormat = "General" Selection.Replace What:=",", Replacement:="." But nothing is happening. Column P still has comma as decimal separator. What am I doing wrong? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma replacement
On May 19, 2:40 pm, Raul Sousa
wrote: I am in Europe. I use by default coma as decimal separator. In a particular situation I need to replace the coma by point. I have this code: Columns("p:p").Select Selection.NumberFormat = "General" Selection.Replace What:=",", Replacement:="." But nothing is happening. Column P still has comma as decimal separator. What am I doing wrong? I think this is failing because the comma is not actually part of the contents of the cell, unless it is formatted as Text. In all number formats the comma is 'virtual' - for display purposes only. In such case, you will either need to build a Custom format that replaces the comma with a dot or the cells will need to be formatted as text and actual periods inserted were desired (probably not the way you want to go). Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma replacement
Unfortunately it does not work.
If do this operation by code, it does not work, Even if I format that column as text. But, If I do it manually, this is, select the column, and set the format to general. Then go to 'Edit', 'Replace', put a ',' in the Find what and a '.' in the Replace With and then hit 'Replace All'. It works I think it should also work with this code. "T Lavedas" wrote: On May 19, 2:40 pm, Raul Sousa wrote: I am in Europe. I use by default coma as decimal separator. In a particular situation I need to replace the coma by point. I have this code: Columns("p:p").Select Selection.NumberFormat = "General" Selection.Replace What:=",", Replacement:="." But nothing is happening. Column P still has comma as decimal separator. What am I doing wrong? I think this is failing because the comma is not actually part of the contents of the cell, unless it is formatted as Text. In all number formats the comma is 'virtual' - for display purposes only. In such case, you will either need to build a Custom format that replaces the comma with a dot or the cells will need to be formatted as text and actual periods inserted were desired (probably not the way you want to go). Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma replacement
On May 20, 4:32 am, Raul Sousa
wrote: Unfortunately it does not work. If do this operation by code, it does not work, Even if I format that column as text. But, If I do it manually, this is, select the column, and set the format to general. Then go to 'Edit', 'Replace', put a ',' in the Find what and a '.' in the Replace With and then hit 'Replace All'. It works I think it should also work with this code. "T Lavedas" wrote: On May 19, 2:40 pm, Raul Sousa wrote: I am in Europe. I use by default coma as decimal separator. In a particular situation I need to replace the coma by point. I have this code: Columns("p:p").Select Selection.NumberFormat = "General" Selection.Replace What:=",", Replacement:="." But nothing is happening. Column P still has comma as decimal separator. What am I doing wrong? I think this is failing because the comma is not actually part of the contents of the cell, unless it is formatted as Text. In all number formats the comma is 'virtual' - for display purposes only. In such case, you will either need to build a Custom format that replaces the comma with a dot or the cells will need to be formatted as text and actual periods inserted were desired (probably not the way you want to go). Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ If the comma is actually in the text in the column, which your statements about the manual operation proves is true, then the code technique should work. A recorded macro for the manual operation results in the following ... Columns("P:P").Select Selection.Replace What:=",", Replacement:=".", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False I think the significant difference is the LookAt:=xlPart, which is needed to find commas as *part* of another string. Maybe that's the problem. Tom Lavedas =========== http://members.cox.net/tglbatch/wsh/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract the text between last comma and last but one comma. | Excel Worksheet Functions | |||
Using comma inside the comma delimited text in Data Validation/Sou | Excel Programming | |||
Comma Delimited-need comma at beginnng & end | Excel Discussion (Misc queries) | |||
Replacement | Excel Programming | |||
Excel How do I create a comma delineated xls file to a comma delineated. | Excel Discussion (Misc queries) |