Getting info from a cell after the last letter appears...

**swade730** · 01-13-2014, 09:27 PM

If you want to extract the numbers from a cell BUT the numbers always start after the last letter...

How would you write a code to just extract the numbers?

for example...

24Macros113

So after the letter S, 113 should be pulled from the cell.

Any help would be appreciated.

**apo** · 01-13-2014, 10:00 PM

Hi..

Try this..

Private Sub CommandButton1_Click()
    Dim objRegex, n
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "\d*\d$"
        For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
            Set myMatches = .Execute(Cells(i, 1))
            For Each n In myMatches
                    Cells(i, 2).Value = n
            Next n
        Next i
    End With
End Sub

Try the attached..

**mgs73** · 01-13-2014, 10:50 PM

This should do it:

Option Explicit

Public Sub Test()
  Dim x As Long
  
  x = GetNumber("24Macros113")
End Sub

Public Function GetNumber(x As String) As Long
  Dim NumberLength As Long
  
  NumberLength = 1
  
  Do While IsNumeric(Right(x, NumberLength))
    NumberLength = NumberLength + 1
  Loop
  GetNumber = CLng(Right(x, NumberLength - 1))
End Function