convert text with (.) to number with (,)

[GoncaloM]

I have a cell in text format with the value:
145.037738007218

and I need to convert the cell to number and the value to:
145,037738007218 or
145,04

so that I can make operations with it without the excel assuming it is a big number.


I tryed
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Selection.NumberFormat = "#.##"

...

I can only get a value like:
145037738007218
or something like
1,4E14

a code in vba would be helpfull.

Thank you very much

[SamT]

Hmmm. Excel stores all numbers as "Big" numbers and performs all math on the "Big" number, even if you format the cell to only show 3 digits.

You can display the number in the cell as decimal fractions of thousands, millions, etc.

Cell Format = Display
"0.0,\k" = 145037738007.2k
"#,##0.0,\k" = 145,037,738,007.2k
"0.00,,\m" =145037738.00m
"0,,,\B" = 145038B (note rounding)
"$0.000,,,,\T" = $145.038T

In all those cases, Excel is using the number as 145037738007218

[GoncaloM]

This may help:

The value 145.037738007218 was calculated and exported to .xls by a software using sql.

Why can't the excel interpret the (.) as (,).

My regional settings are setup as (,) for decimals and (.) for thousands.

Thanks






Hmmm. Excel stores all numbers as "Big" numbers and performs all math on the "Big" number, even if you format the cell to only show 3 digits.

You can display the number in the cell as decimal fractions of thousands, millions, etc.

Cell Format = Display
"0.0,\k" = 145037738007.2k
"#,##0.0,\k" = 145,037,738,007.2k
"0.00,,\m" =145037738.00m
"0,,,\B" = 145038B (note rounding)
"$0.000,,,,\T" = $145.038T

In all those cases, Excel is using the number as 145037738007218

[Paul_Hossler]

Why can't the excel interpret the (.) as (,).

Probably because the data is coming over as text and to Excel the 'dot' is just another letter/character, not a separation of decimals


Try something like this



Sub drv()
MsgBox MakeNumber("145.037738007218")
MsgBox 3 * MakeNumber("145.037738007218")
End Sub

Function MakeNumber(S As String) As Double
MakeNumber = CDbl(Replace(S, ".", Application.International(xlDecimalSeparator)))
End Function

[SamT]

Select the Range of Cells to convert to numbers before running


Sub ConvertText2Number()
Dim Cel as Range

On Error Resume Next
For each Cel in Selection
Cel.Value = CDbl(Cel)
Next
End Sub

[GoncaloM]

Thank you for your efforts Paul_Hossler and SamT,

CDbl(x) doesn't work.

I need this to convert units in a table, exported from a software, which only allows me to export with US units.
Hence the problem with (.) and (,).
This is the code I have now, but to avoid problems with (.) I can only do it by changing (.) with (,) manually:
- selecting data; Ctrl+F; (.) by (,)

I want to add this operation to the code but I can't find the solution.

Dim c, l As Integer
Sub Unitconv()


c = 2 'column index

Do
l = 3 'row index

If Cells(l, c) = "psig" Then

pressure 'sub

ElseIf Cells(l, c) = "F" Then

temperature 'sub

ElseIf Cells(l, c) = "in" Then

thickness 'sub

ElseIf Cells(l, c) = "MPY" Then

corrrate 'sub

Else

End If
c = c + 1
l = 3
Loop While Not Cells(l, c) = ""


End Sub
___________________
Sub pressure()
Do

Cells(l, c - 1).Select
x = Cells(l, c - 1)
y = Round(x / 14.503, 2)
Selection.NumberFormat = "0.00;;0.00"
Cells(l, c - 1).Value = y
Cells(l, c).Value = "barg"
l = l + 1
Loop While Cells(l, c) = "psig"
End Sub
____________________
Sub temperature()
Do

Cells(l, c - 1).Select
x = Cells(l, c - 1)
y = Round((x - 32) / 1.8, 2)
Cells(l, c - 1).Value = y
Selection.NumberFormat = "0.00;;0.00"
Cells(l, c).Value = "ºC"
l = l + 1
Loop While Cells(l, c) = "F"

End Sub
_________________________
Sub thickness()
Do

Cells(l, c - 1).Select
x = Cells(l, c - 1)
y = Round(x * 25.4, 2)
Cells(l, c - 1).Value = y
Selection.NumberFormat = "0.00;;0.00"
Cells(l, c).Value = "mm"
l = l + 1
Loop While Cells(l, c) = "in"
End Sub
____________________________
Sub corrrate()
Do

Cells(l, c - 1).Select
x = Cells(l, c - 1)
y = Round(x * 25.4 / 1000, 2)
Cells(l, c - 1).Value = y
Selection.NumberFormat = "0.00;;0.00"
Cells(l, c).Value = "mm/y"
l = l + 1
Loop While Cells(l, c) = "MPY"
End Sub
____________________


Select the Range of Cells to convert to numbers before running


Sub ConvertText2Number()
Dim Cel as Range

On Error Resume Next
For each Cel in Selection
Cel.Value = CDbl(Cel)
Next
End Sub

[snb]

Use:


Sub M_snb()
with Columns(1)
.NumberFormat = "0.00"
.Replace ".", ","
.TextToColumns
end with
End Sub

[Paul_Hossler]

1. If you use the [#] icon, you can paste your code between the [ CODE ] and [ / CODE ] to make it easier to read

2. Usually there's no need to select a cell

3. I think it's good practice to Dim Variables

4. Assuming that Cells(l, c - 1) contains the string that should be a number, something like this (untested) could work




Function MakeNumber(S As String) As Double
MakeNumber = CDbl(Replace(S, ".", Application.International(xlDecimalSeparator)))
End Function


Sub pressure()

Dim x as Double, y as Double


Do Cells(l, c - 1).Select
x = MakeNUmber (Cells(l, c - 1))
y = Round(x / 14.503, 2)

Selection.NumberFormat = "0.00;;0.00"
Cells(l, c - 1).Value = y
Cells(l, c).Value = "barg"
l = l + 1

Loop While Cells(l, c) = "psig"

End Sub
____________________