[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

 

[rlv01]

One way

Sub FormatTest()
    Dim MyStr As String

    MyStr = "Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625"
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))

    Debug.Print MyStr
End Sub

 

[Dajnoxes]

One way

Sub FormatTest()
    Dim MyStr As String

    MyStr = "Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625"
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))

    Debug.Print MyStr
End Sub

Thanks, nice work

I checked it and it doesn't work with dots but I will replace "." to "," then I will run your script and after that I will replace "," to "."
It works perfect!

 

[rlv01]

The code as posted was tested and works.

Before: "Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625" After: "Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.17"

The presence of periods (dots) should not make any difference. You should not have to replace anything unless the example string you posted is different from the actual strings you are using. In which case some additional string cleanup might be needed. For example, if the string has extra spaces or hidden characters at the end.

Sub FormatTest2()
    Dim MyStr As String

    MyStr = Trim(Range("A1").Value)
    
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))
    
    Range("A2").Value = MyStr
End Sub

Book1
	A
1	Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625
2	Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.17
3
4
5
Sheet6

 

[Dajnoxes]

The code as posted was tested and works.

Before: "Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625" After: "Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.17"

The presence of periods (dots) should not make any difference. You should not have to replace anything unless the example string you posted is different from the actual strings you are using. In which case some additional string cleanup might be needed. For example, if the string has extra spaces or hidden characters at the end.

Sub FormatTest2()
    Dim MyStr As String

    MyStr = Trim(Range("A1").Value)
   
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))
   
    Range("A2").Value = MyStr
End Sub

Book1
	A
1	Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625
2	Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.17
3
4
5
Sheet6

When I'm running this code when I have dots in string I'm getting error: Type mismatch

I changed On the advanced tab, under Editing options separator to "." but it still doesn't work :/

 

[rlv01]

When I'm running this code when I have dots in string I'm getting error: Type mismatch

I changed On the advanced tab, under Editing options separator to "." but it still doesn't work :/

Can you post the actual string that does not work, along with your code that produces the error, and the line it fails at?

 

[Dajnoxes]

Can you post the actual string that does not work, along with your code that produces the error, and the line it fails at?

It's the same as your 1:1

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

Sub FormatTest2()
    Dim MyStr As String

    MyStr = Trim(Range("A1").Value)
    
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))
    
    Range("A2").Value = MyStr
End Sub

 

[rlv01]

1. Which line of code produces the Type Mismatch error?

2. Run this test code

Sub FormatTest2()
    Dim MyStr As String
    Dim X As Variant
    
    X = Range("A1").Value
    Debug.Print "X is type " & TypeName(X)
    
    MyStr = Trim(CStr(X))
    Debug.Print "MyStr = '" & MyStr & "'"
    Debug.Print "Last Char = " & Asc(Right(MyStr, 1)) & " ('" & Right(MyStr, 1) & "')"
    
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))
    Debug.Print "MyStr (after)  = '" & MyStr & "'"
    
    Range("A2").Value = MyStr
End Sub

3. Post the results of the debug.print statements. My results:
X 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') MyStr (after) = 'Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.17'

Book1
	A
1	Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625
2
3
4
Sheet6

 

[Dajnoxes]

1. Which line of code produces the Type Mismatch error?

2. Run this test code

Sub FormatTest2()
    Dim MyStr As String
    Dim X As Variant
  
    X = Range("A1").Value
    Debug.Print "X is type " & TypeName(X)
  
    MyStr = Trim(CStr(X))
    Debug.Print "MyStr = '" & MyStr & "'"
    Debug.Print "Last Char = " & Asc(Right(MyStr, 1)) & " ('" & Right(MyStr, 1) & "')"
  
    MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))
    Debug.Print "MyStr (after)  = '" & MyStr & "'"
  
    Range("A2").Value = MyStr
End Sub

3. Post the results of the debug.print statements. My results:
X 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') MyStr (after) = 'Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.17'

Book1
	A
1	Line from Point N50424 to N52525 has fixed length. Line diameter 4.5 Max Line diameter 6.166666666625
2
3
4
Sheet6

1. In this line:

MyStr = Replace(MyStr, Split(MyStr, " ")(UBound(Split(MyStr, " "))), Round(CDbl(Split(MyStr, " ")(UBound(Split(MyStr, " ")))), 2))

3.I t's all what I'm getting

X 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')

 

[rlv01]

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)