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

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.
--
HTH...

Jim Thomlinson


"Dave M" wrote:

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

On Mon, 13 Aug 2007 11:16:01 -0700, Dave M
wrote:

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