Find and Replace Problem

jborges

New Member
Joined
Apr 22, 2002
Messages
13
I trying to program a Subroutine to replace a "." for a "," in a column of imported data in order to convert this text into numbers with decimals:

1.0000 --> 1,0000

If I do manually or with a Macro I get the result, if I use the same code than in the Macro for the subroutine I get an undesired result:

1.0000 --> 10000

Do you any idea ?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Not sure, should
really be<pre> 10,000.00?</pre>
Or do you want the , to represent a decimal?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-29 15:45
 
Upvote 0
I would like teh "," to represent a decimal separator.

Thanks
On 2002-04-29 15:44, NateO wrote:
Not sure, should
really be<pre> 10,000.00?</pre>
Or do you want the , to represent a decimal?

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-29 15:45
 
Upvote 0
Howdy, better late than never? I assume your imported numbers are operating as text for some sort of visual effect (making numeric formatting a bogus option). To keep the numbers as text and create the effect you're looking for, I used the following procedure:

<pre>
Sub rplc()
Dim cl As Range
For Each cl In [a1:a500] 'change to your range
If Not IsEmpty(cl.Value) Then _
cl.Value = "'" & Application.Substitute(cl.Value, ".", ",")
Next cl
End Sub</pre>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top