| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't replace commas with period
Hi Everyone
Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
Maybe the values in the cells don't contain commas. Maybe they're just plain
old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
ps
Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns could be re-written: Range("J3:L60").Replace What:=chr(160), Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByColumns You could drop the .select's from all your code. And the chr(160) is a little more self-documenting. John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
Hi Dave
Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
I use the dot for my decimal point and I could change that to a comma with no
trouble. What error do you get? Maybe you could try just changing the commas to nothing. Depending on how your numbers are grouped, it may even be a better choice??? John wrote: Hi Dave Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
Hi Dave
I tried changing commas to nothing,does't work. The error message is: "The formula you typed contains an error." For information about fixing common formula problems, click Help. with two more line of information. Regards John "Dave Peterson" wrote in message ... I use the dot for my decimal point and I could change that to a comma with no trouble. What error do you get? Maybe you could try just changing the commas to nothing. Depending on how your numbers are grouped, it may even be a better choice??? John wrote: Hi Dave Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
Hi Dave
I changed the comma symbal , for Chr(44) and it seem to work. Will need to play with it to be sure. It's your idea with Chr(160) that made me try that. Thanks Dave John "Dave Peterson" wrote in message ... I use the dot for my decimal point and I could change that to a comma with no trouble. What error do you get? Maybe you could try just changing the commas to nothing. Depending on how your numbers are grouped, it may even be a better choice??? John wrote: Hi Dave Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
Me again
I tried it with fresh data 3 times and it works perfectly. This is my new line: Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart, SearchOrder:= xlByColumns Many thanks Dave Regards John "John" wrote in message ... Hi Dave I changed the comma symbal , for Chr(44) and it seem to work. Will need to play with it to be sure. It's your idea with Chr(160) that made me try that. Thanks Dave John "Dave Peterson" wrote in message ... I use the dot for my decimal point and I could change that to a comma with no trouble. What error do you get? Maybe you could try just changing the commas to nothing. Depending on how your numbers are grouped, it may even be a better choice??? John wrote: Hi Dave Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
I have no idea why that would matter.
John wrote: Me again I tried it with fresh data 3 times and it works perfectly. This is my new line: Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart, SearchOrder:= xlByColumns Many thanks Dave Regards John "John" wrote in message ... Hi Dave I changed the comma symbal , for Chr(44) and it seem to work. Will need to play with it to be sure. It's your idea with Chr(160) that made me try that. Thanks Dave John "Dave Peterson" wrote in message ... I use the dot for my decimal point and I could change that to a comma with no trouble. What error do you get? Maybe you could try just changing the commas to nothing. Depending on how your numbers are grouped, it may even be a better choice??? John wrote: Hi Dave Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can't replace commas with period
Hi Dave
I have no idea why that would matter.< I wish someone knew, you're telling me it's a fluke,just hope it keeps working. Regards John "Dave Peterson" wrote in message ... I have no idea why that would matter. John wrote: Me again I tried it with fresh data 3 times and it works perfectly. This is my new line: Range("J3:L60").Replace what:=Chr(44), Replacement:=Chr(46), LookAt:=xlPart, SearchOrder:= xlByColumns Many thanks Dave Regards John "John" wrote in message ... Hi Dave I changed the comma symbal , for Chr(44) and it seem to work. Will need to play with it to be sure. It's your idea with Chr(160) that made me try that. Thanks Dave John "Dave Peterson" wrote in message ... I use the dot for my decimal point and I could change that to a comma with no trouble. What error do you get? Maybe you could try just changing the commas to nothing. Depending on how your numbers are grouped, it may even be a better choice??? John wrote: Hi Dave Thank you for your help. The commas are decimal separators and I need to change it to a period. I did try manually to type the commas and to see if it would remove them but to no availed. If I use the "Find/Replace" menu, I can make it work but I get an error message. even if I get an error, it still makes the corrections. Regards John "Dave Peterson" wrote in message ... Maybe the values in the cells don't contain commas. Maybe they're just plain old numbers formatted to show commas (either as the thousands separator or the decimal point????). John wrote: Hi Everyone Below are my codes that I use to clean my data coming from the internet. They all work except the first one (Find all the commas "," and replace with a period (.) ) Can anyone tell me why? Option Explicit Sub Clean_Data() Range("J3:L60").Select Cells.Replace What:=",", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByColumns Range("J3:L60").Select Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns ' ALT-0160 Range("J3:L60").Select Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _ xlByColumns End Sub Regards John -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| between commas | Excel Programming | |||
| Excel - Find and Replace from rows to separation by commas | Excel Discussion (Misc queries) | |||
| find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
| Period to Period percentage change? | Excel Discussion (Misc queries) | |||
| Replace dot with commas | Excel Programming | |||
![John[_22_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
