CODEWITHSUNDEEP
excelvbanumber-formatting
Alvaro Morales
Alvaro Morales

Reputation: 1925

VBA number formatting (with commas as decimal separator)

The main problem started when I wanted to "convert to number" by the green triangle (I know I can do it by hand, but there are a lot of cells like that and in the future I only want to use code).

enter image description here

So I wanted to do it by code, and I came across with this code that helps, but I have a problem with the number format which removes the decimal numbers.

enter image description here

Sub Valor3()

Dim LastRow As Long, i As Long

LastRow = Sheets("Hoja3").Range("A" & Rows.Count).End(xlUp).Row

'Sheets("Hoja3").Range("A1:A" & LastRow).NumberFormat = "# ##0,00"

For i = 1 To LastRow
    If Val(Sheets("Hoja3").Range("A" & i).Value) <> 0 Then _
    Sheets("Hoja3").Range("A" & i).Formula = _
    Val(Sheets("Hoja3").Range("A" & i).Value)
Next i

End Sub

I've been trying many formats but none of them seems to help. It might be because here we use the comma as a decimal separator and there is no miles separator.

What number format would help me?

Upvotes: 0

Views: 14408

Answers (2)

Justyna MK
Justyna MK

Reputation: 3563

I know you are looking for VBA solution, but here's a small Excel trick that you might find useful:

  1. Enter 1 (numeric value) somewhere in the file and copy it:

enter image description here

  1. Select your range (A1:A6) and go to Paste > Paste Special > select Multiply:

enter image description here

  1. The final result is all your text values being converted to numbers:

enter image description here

The same trick will work with other combinations, e.g. Operation: Add while having 0 copied, etc.

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

The issue is that you use Val function in combination with a non-us-english decimal separator, which is not a proper solution to your issue.

The Val function recognizes only the period ( .) as a valid decimal separator. When different decimal separators are used, as in international applications, use CDbl instead to convert a string to a number.

Source: Microsoft documentation Val function.

Since the Val function does not convert a text into a value but extracts The Val function only works with a dot . as decimal separator.

Example:

Val("2.55") 'will return 2.55 as number
Val("2,55") 'will return 2 as number (because it cuts off all text and the comma is not considered as decimal separator)

To get rid of the green triangle and convert a number that is saved as text into a real number properly, use the following:

Option Explicit

Public Sub ConvertNumberAsTextIntoRealNumber()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Hoja3")

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    With ws.Range("A1", "A" & LastRow)
        .NumberFormat = "# ##0.00"  'set your desired number format
        .Value = .Value  'this will in most cases already convert to real numbers.
    End With

    'But if your numbers are hard coded to text and begin with a `'` you need the following additionally:
    Dim iRow As Long
    For iRow = 1 To LastRow
        With ws.Cells(iRow, "A")
            If IsNumeric(.Value) Then  'can the value be interpreted as a number
                If .Value <> 0 Then  'is the value not zero
                    .Value = CDbl(.Value)  'then convert it into a real number
                End If
            End If
        End With
    Next iRow
End Sub

Upvotes: 2

Related Questions