[VBA] Decimal place of number in string

[Dajnoxes]

Hi,

I have string:

Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625

Except this string I have a lot of different strings in my sheet but I have issue only with this one because after every export csv file
Max Line diameter is changing,

Max Line diameter 6.166666666625
sometimes it.s 6.16, next time it wil be 6.1666666666666662525

Can I somehow make it in VBA that will change this string to have 2 place decimal

for example:
when Max Line Diameter 6.1111111111 it will change to 6.11
when Max Line Diameter 3.5934432424 it will change to 3.59

I hope you understand what I mean :D

Best Regards,
Dajnoxes

 

[Dajnoxes]

See what this one does:

Sub FormatTest3()
    Dim MyStr As String
    Dim X As Variant
    Dim S As String
    Dim SA As Variant, D As Double
  
    X = Range("A1").Value
    Debug.Print "Cell Data is type " & TypeName(X)
  
    MyStr = Trim(CStr(X))
    Debug.Print "MyStr = '" & MyStr & "'"
    Debug.Print "Last Char = " & Asc(Right(MyStr, 1)) & " ('" & Right(MyStr, 1) & "')"
  
    SA = Split(MyStr, " ")
    X = SA(UBound(SA))
    Debug.Print "X = " & X & " (Type " & TypeName(X) & ")"
  
    D = CDbl(X)
    Debug.Print "D = " & D & " (Type " & TypeName(D) & ")"
    Debug.Print "Rounded D = " & Round(D, 2)
  
    MyStr = Replace(MyStr, SA(UBound(SA)), Round(D, 2))
    Debug.Print "MyStr (after)  = '" & MyStr & "'"
  
    Range("A2").Value = MyStr
End Sub

(note: everything I've posted so far works without error for me. On 2 different PCs)

I'm still getintg the same error

Error line: D = CDbl(X)

Cell Data is type String
MyStr = 'Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625'
Last Char = 53 ('5')
X = 6.166666666625 (Type String)

 

[rlv01]

I'm still getintg the same error

Error line: D = CDbl(X)

Replace it with

    D = Val(X)

And report results.

 

[Dajnoxes]

Replace it with

    D = Val(X)

And report results.

It works,

Cell Data is type String
MyStr = 'Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625'
Last Char = 53 ('5')
X = 6.166666666625 (Type String)
D = 6,166666666625 (Type Double)
Rounded D = 6,17
MyStr (after) = 'Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6,17'

Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6,17

Thanks for your help

 

[rlv01]

Rounded D = 6,17

So your PC is using the comma as the decimal separator instead of the period? That explains a lot. Why did your original post show only periods being used?

 

[Dajnoxes]

So your PC is using the comma as the decimal separator instead of the period? That explains a lot. Why did your original post show only periods being used?

I changed manually separator in Excel to period but originally I have comma
When I write 1.15 it sees as decimal separator.
Thankfully, it works now