VBA to extract last number from string

[JohnDouglas]

Any ideas how to extract the last number from these strings???

I guess the VBA would need to right trim. which i can do, but not sure how to then find the final space " " and extract all the digits after it
... any ideas?


example 1: "Page: First | -10 | 51 "

example 2: "Page: First | -10 | 231 232 233 234 235 236 "

Thanks for you help

John

 

[Purple Youko]

1 Find the length of the string using len()
2 set up a loop that will count back from the right using mid() until it finds a space
3 use right() to get everthing to the right of that position.

 

[Purple Youko]

Something like this.

Private Sub CommandButton1_Click()
  Dim A As String
  Dim B As Integer
  Dim C As Integer
  Dim D As String
  A = Range("A1").Value
  B = Len(A)
  For Position = B To 1 Step -1
    C = Mid(A, Position, 1)
    If C = " " Then
      D = Right(A, B - Position)
      Range("A2").Value = D
      Exit Sub
    End If
  Next Position
End Sub

Just place a control button on your worksheet, put your value in cell A1 and press the button. The code will search out the final space in the value and report everything to its right in cell A2

 

[JohnDouglas]

many thanks for your response ......... i get runtime error 13 though when i try this.

 

[Purple Youko]

That's strange.

This code was copied directly out of a VBA code window in which it was working perfectly

 

[Purple Youko]

Oops!

Just tested it again and found that i dimmed C wrong. it should have been a string. try this.

Private Sub CommandButton1_Click()
  Dim A As String
  Dim B As Integer
  Dim C As String
  Dim D As String
  A = Range("A1").Value
  B = Len(A)
  For Position = B To 1 Step -1
    C = Mid(A, Position, 1)
    If C = " " Then
      D = Right(A, B - Position)
      Range("A2").Value = D
      Exit Sub
    End If
  Next Position
End Sub

 

[JohnDouglas]

fantastic, it still wasn't working but then i realised a trim was needed.

thanks for your help with this, i'm learning more every day!

cheers

john

final code

Private Sub CommandButton1_Click()
  Dim A As String
  Dim B As Integer
  Dim C As String
  Dim D As String
  A = Range("A1").Value
  A = Trim(A)
  B = Len(A)
  For Position = B To 1 Step -1
    C = Mid(A, Position, 1)
    MsgBox C
    If C = " " Then
      D = Right(A, B - Position)
         Range("A2").Value = D
      Exit Sub
    End If
  Next Position
End Sub

 

[jindon]

Hi

UDF
use in cell like
=john(A1)

Function john(txt As String) As Long
Dim x
x = Split(Trim(txt), Chr(32))
john = Val(x(UBound(x)))
End Function

 

[JohnDouglas]

thanks jindon