Format of a number on an Excel Sheet ...

[Rodie]

Hi all [flush]

I work on VBA Excel. From a text file, I copy prices such 1000,50 $
The problem is that : when I copy this price on an Excel sheet (as a string), I want that the comma is recognized like the decimal separator. Also, it will work only if the Excel sheet configuration defines effectively the comma as the decimal separator.
If the dot is the decimal separator for the sheet, it will not work.

And of course, I would like my program works on the 2 types of configuration : dot and comma (so replace . by , or , by . is excluded).
How can I solve this problem ???
Is there a way to set the sheet configuration from VBA ?

Thanks a lot in advance
Rody

 

[SkipVought]

Rody,

You stated, "From a text file, I copy prices such 1000,50 $"

Surely you are not using Copy 'n' Paste form a Text file. Are you importing the text file?

I was able to use Edit/Replace to remove the [Space]$ and change comma to point. VOLA!

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Rodie]

Hi Skip

Thanks a lot to you. I effectively IMPORT the text file : I open it with Excel and in a cell, I obtain the original price like 1000,50 $.
The problem is not the spaces and the symbol $ because I remove them from the cell. I just keep the string 1000,50 which I copy on a cell of another sheet of another workbook (formatted as number, because I have to add prices)
And then, I don't know what is the configuration of Excel on the computer : . as decimal separator or , ???

And it's no use changing comma to point, or the opposite, because I don't know the decimal separator of the computer. That's why I think I must format the cell manually from VBA and force the cell to have . or , as decimal separator.

I hope I'm clear enough. Sorry, but I'm not fluent ...
Anyway, thanks a lot to you. Have a nice end of summer
Rodie

 

[PHV]

I don't know the decimal separator
Have you tried to play with the IsNumeric function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rodie]

Hi PHV

I did not use IsNumeric(). I know I use numbers. But what do you mean ? I don't understand you ... sorry ...

Rodie

 

[PHV]

Here the basic idea:
Dim s As String, c As Currency
s = "1000,50"
If IsNumeric(s) Then
c = CCur(s)
Else
c = CCur(Replace(s, ",", "."))
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Open the Windows COntrol Panel-REgional Settings. Somewhere in there, (Customize regional settings on mine) you can edit things like Decimal Symbol and Digit Grouping Symbol.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Rodie]

PHV, your suggestion is totally right. It works but now, I must take account of the fact that the price I use can contain decimal and thousands delimiters at the same time ! (for example : 11,500.75 or 11.500,75.
How can I differentiate those 2 delimiters ?
Indeed, if I do as you suggested, replace , by . , it won't work.

Moreover, I can't customize the regional settings of the Workbook because my program must work on all computers in the whole Europe. So, Skip, I think what you suggest can't solve my problem.

Is there a way to solve that ?
Thanks to you 2 and have a nice week-end.
Rodie

 

[PHV]

Something like this ?
If IsNumeric(s) Then
c = CCur(s)
Else
c = CCur(Replace(Replace(s, ".", ""), ",", "."))
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rodie]

Thanks a lot PHV.

Just one thing : how can I know what decimal separator is used by the Worksheet ? (and not forcerly according to the regional settings because the user can customize it and change comma by dot for example)
Thanks in advance
Rodie

 

[PHV]

I think that VBA always play with dot for decimal separator, despite your locale.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rodie]

Thanks PHV. You are apparently right.

But I found a method to get the decimal separator :
GetLocaleInfo() API using the LOCALE_SDECIMAL ... But I did not succeed in using it. Do you know who I could do ?

Anyway, thanks a lot for the work you do on this forum. Your answers are concise, clear and very helpful. Unfortunately, my computing course ends tomorrow and after, I restart my studies. I wish you long life and happiness

Rodie says bye