Change dots to commas

[looney]

Hi,

I am busy with a lot of data from different users. They fill in some numbers, but some use the dot from the nummeric keypad as seperator. where it needs to be a comma.

For this I use google forms, It almost doing the trick but every user is using it differently. ", " or "." The numbers with a dot are shown as a text and the ones with a comma as numbers.

It seems I just need a vb script that can change the dots into commas in a certain range of cells.

Who can help me out with this?

 

[SirJB7]

Hi, looney!
Had tried to use the built-in macro recorder and you've got this code:

Option Explicit

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:B10").Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Regards!

 

[looney]

Thanks SirJB7!

I had something similar done...

As you probably see I tried to change the format afterwards. Why?
The numbers are seen as text, but my solution takes ages to execute and a error pops up when there are letters involved instead of numbers.

I hope you have a better idea?

Sub replacedot()

    With Range("P:AI")
    .Replace What:=".", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, matchCase:=False
    End With
Range("P2:AI655336").Select
For Each xCell In Selection
Selection.NumberFormat = "0,00"
xCell.Value = CDec(xCell.Value)
Next xCell

End Sub

 

[SirJB7]

Hi, looney!
Not for the code itself but maybe for the source. Questions:
a) Do you have those replacements to be done by workbook, worksheet, row ranges or single rows?
b) Do you yet have the input data into a worksheet or you import from other sources?
c) If yet in the ws, how did it get there, typed or copied & pasted?
Regards!

 

[kchiba]

Hi,

from my experience, if the number is using "." as a thousand separator, remove the "." and do not replace it with anything as the "," is a function of the number formatting.

cheers

kanti

 

[SirJB7]

@kchiba
Hi!
I thought that at a first glance, but then I realized that looney wrote at the beginning

It almost doing the trick but every user is using it differently. ", " or "." The numbers with a dot are shown as a text and the ones with a comma as numbers.

It seems I just need a vb script that can change the dots into commas in a certain range of cells

So he'd be trying to convert numbers displayed as text because of a wrong decimal separator into actual numbers, and no replacing or undoing any format applied as digit separator.
Regards!

 

[kchiba]

SirJB7,

So he'd be trying to convert numbers displayed as text because of a wrong decimal separator into actual numbers, and no replacing or undoing any format applied as digit separator.

By replacing the "." with "," you are not changing it to a number, that still keeps it as text, you are better off replacing "." with nothing.

 

[SirJB7]

@kchiba
Hi!
Yeap, you're right, that was my 1st attempt, then OP posted his code and the last part of it is this:

Range("P2:AI655336").Select
For Each xCell In Selection
Selection.NumberFormat = "0,00"
xCell.Value = CDec(xCell.Value)
Next xCell

Despite of the running time and the efficiency, it solves the data type issue.
Regards!

EDITED

 

[looney]

Hi!
Thanks for all the thinking with me!

Seems that I am stuck with the slow running time.

So I tried to get some answers on the googleforum for googleforms. The best solution for me now is to force the region settings of the google spreadsheet to US. With this I can also force users with a validation setting on googleforms to use a dot as separator. Bit confusing for them as they are used to a comma, but they will be able to use the nummeric keypad on a normal way. And all my data will be numbers instead of text!

 

[SirJB7]

Hi, looney!
As I wrote earlier to kchiba, running time will sure be an issue, given for the 20x65536 (more than 130K, check that you typed a 5 in excess, 655536, so you're running it 1.3M times) loop.
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!