VB Script to Remove Letters from a number

[Guest]

Hello,

I need to create a script that will allow me to remove two letters at the
end of a number. Most of the time, the number appears as 1234567.00, but
sometimes it comes as 1234567.00CR. I need to remove the two letters at the
end (the letters can change) and leave the decimal and the numbers.

Thanks for your Help

Dave

 

[Guest]

Here is a function that takes a cell and returns the value less the last two
characters if they are not numeric.

Public Function RemoveLetters(ByVal Cell As Range) As Double

If Not IsNumeric(Right(Cell.Value, 2)) Then
RemoveLetters = CDbl(Left(Cell.Value, Len(Cell) - 2))
Else
RemoveLetters = Cell.Value
End If

End Function

This function assumes that your data is not padded with blank characters at
the end.

 

[Ron Rosenfeld]

Hello,

I need to create a script that will allow me to remove two letters at the
end of a number. Most of the time, the number appears as 1234567.00, but
sometimes it comes as 1234567.00CR. I need to remove the two letters at the
end (the letters can change) and leave the decimal and the numbers.

Thanks for your Help

Dave

Option Explicit
Sub StripNums()
Dim oRegex As Object
Const sPattern As String = "[A-Za-z]{2}$"

Const s1 = "12345.00"
Const s2 = "12345.00CR"

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Pattern = sPattern

Debug.Print "s1", oRegex.Replace(s1, "")
Debug.Print "s2", oRegex.Replace(s2, "")
End Sub


--ron