Can't replace commas with period

  • Thread starter Thread starter John
  • Start date Start date
J

John

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
 
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????).
 
ps
Range("J3:L60").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByColumns
Click to expand...

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.
 
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
 
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???
 
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
 
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
 
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 said:
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
Click to expand...
 
I have no idea why that would matter.
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
Click to expand...
 
Back
Top