Converting Text to numbers

SeppT03 

There is a way to convert the text from its current form 10,562.98 to 10562,98 as a number format in Excel. You can use the SUBSTITUTE function to replace the comma with nothing and the dot with a comma.

Here’s an example formula that you can use:

=VALUE(SUBSTITUTE(SUBSTITUTE(A1,",",""),".",","))

This formula will first remove the comma by replacing it with nothing using SUBSTITUTE(A1,",",""). Then it will replace the dot with a comma using SUBSTITUTE( ... ,".",","). Finally, it will convert the resulting text into a number using the VALUE function.

You can use this formula for all the cells that contain numbers in the 10,562.98 format.

Just replace A1 with the cell reference that contains the number you want to convert.

OR…you can use VBA (Visual Basic for Applications) to convert text to numbers in Excel.

Here’s an example VBA code that you can use to convert the text from its current form 10,562.98 to 10562,98 as a number format:

Sub ConvertTextToNumber()

    Dim cell As Range

    Dim myRange As Range

    Set myRange = Range("A1:A10") ' Change this to the range you want to format

    For Each cell In myRange

        If IsNumeric(cell) Then

            cell.Value = CDbl(Replace(Replace(cell.Value, ",", ""), ".", ","))

        End If

    Next cell

End Sub

You can change the value of this variable to the range of cells that you want to format. In this example, the range is set to A1:A10, but you can change it to any range you want.