SOLVED

# Converting Text to numbers

Copper Contributor

# Converting Text to numbers

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

14 Replies

# Re: Converting Text to numbers

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.

# Re: Converting Text to numbers

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

# Re: Converting Text to numbers

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?

# Re: Converting Text to numbers

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?"...

# Re: Converting Text to numbers

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;".";",")``

# Re: Converting Text to numbers

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?

# Re: Converting Text to numbers

Hi Nicolino,
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.

Regards

Sepp

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Converting Text to numbers

NUMBERVALUE does it - see attachment columns F-G

# Re: Converting Text to numbers

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

# Re: Converting Text to numbers

Glad you have a solution & Thanks for providing feedback

# Re: Converting Text to numbers

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

# Re: Converting Text to numbers

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

In VBA:

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

# Re: Converting Text to numbers

Alternatively, use Data > Text To Columns.

In VBA:

``    Range("...").TextToColumns``

Alternatively

``````    With Range("...")
.Value = .Value
End With``````
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Converting Text to numbers

NUMBERVALUE does it - see attachment columns F-G