Converting Text to numbers

[SeppT03]

I have statements in CSV format converted to text in excel. Pproblem the numbers are seperated with a comma as the thousand seperator and a dot for the decimal  i.e. 10,562.98. They can therefore not be used as numbers and to do calculations.

My system is set up with no thousand seperator and a comma as decimal i.e 10562,98. I dont want to change the settins as this will effect all my other spreadsheets. 

Is there a way to convert this text from its current form 10,562.98 to 10562,98 as a number format?

Appriciate any help I can get, thank you

Regards

Sepp 

[#2]

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

[#3]

@SeppT03  Another option is to use 'Text to Columns' (found under DATA -> Data Tools group).  The first couple pages the default should be ok and then under Advanced you can tell it to recognize the opposite separators:

[#4]

Many roads lead to Rome...and even more roads lead to a result in Excel! :)) "(Y)"

[#5]

so true. I need to try to remember to use that saying about Rome instead of always using the saying about ways to skin a cat ... why in the world do we talk about many ways to skin a cat any how?

[#6]

Hi Nicolino, thank you for your speedy reply, much appriciated. Im not at all clued up in VBA so tried the formula instead however there is an error message I receive "Not trying to type a formula?"...

[#7]

Hi @SeppT03 

"Not trying to type a formula?" => Probably because your formula delimiter isn't the comma but something else like ;. With your TEXT value in A1:

=NUMBERVALUE(A1;".";",")

[#8]

The error message “Not trying to type a formula?” usually appears when you enter a formula into a cell but forget to start it with an equal sign (=). Make sure that you have entered the formula correctly and that it starts with an equal sign.

If you are still receiving this error message after checking the formula, there may be another issue causing the problem. Could you provide more information about the context in which you are using this formula and any other details that might be relevant?

Have you used the other formulas from the other users here? If so, does the same problem appear? ...just ask..which Excel version, operating system, storage medium?

[#9]

@NikolinoDE 

Hi Nicolino,
Thanks again for your response.
The formula is copied and correct, incl (=).

I work on windows 11, and Office 365, 64 bits.

I've attached both files, the CVS and the xlsx, perhaps this makes it easier.

Thanks again for your assistance

Regards

Sepp

[#10 · Accepted solution]

@SeppT03 

NUMBERVALUE does it - see attachment columns F-G

[#11]

@L z. 

Awesome, thank you very much, your assistance is greatly appreciated.

Also thank you to everyone else that has tried to sort this.

Kind regards

Sepp

[#12]

Glad you have a solution & Thanks for providing feedback

[#13]

Hi Nikolino,

I also have a problem of converting the string (from a textbox) "1,234.56" to 1234.56 using VBA. I have tried to convert it both before and after pasting without success so far. I do not understand the bit about "If IsNumeric(cell)" because the problem for me is that it will paste as a string. Using Val("1,234.56") pastes as 1.00 ie VBA has not recognized it as numeric either. I have not managed to convert the string to a number before or after pasting. I think it would be better to convert it before pasting to a variable and leave the txtbox in the form with the comma. I realise the problem is the comma but surely there is some function that handles this?
Sue

[#14]

@Sue 

In Excel itself, you might select the range and replace comma with nothing.

In VBA:

    Range("...").Replace What:=",", Replacement:="", LookAt:=xlPart

[#15]

@Sue 

Alternatively, use Data > Text To Columns.

In VBA:

    Range("...").TextToColumns

Alternatively

    With Range("...")
        .Value = .Value
    End With