Replacing dots with commas using macro

[maxest]

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!

[JNW]

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
>
>

[maxest]

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 numbers 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 numbers that are in text format.

I have tried also to change format of cell in every way but it doesnt work. Works only F2 and Enter.

[JNW]

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
>
>