Solved: Excel 2003 - Need the numer value at the end of a string

[frank_m]

Using VBA (not a formula), I need my Cell A1 value to equal the number value at the end of a string.
Example strings
value equals 1 for these three
"abc-1"
"200-1"
"50-01"

value equals 12 for these three
"100-12"
"ef-012"
"SST-012"
I only used the quote's to demonstrate that these are all strings

I realize that using the minus sign as a separator in the original data likely complicates things, but that's the data layout that I'm required to work with.

Thanks

[mancubus]

if always numbers after "-" will be extracted, try;


Sub extnum()

Dim rng As Range

For Each rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
If IsNumeric(rng) Then
rng.Offset(, 1) = rng
Else
rng.Offset(, 1) = Right(Trim(rng), Len(Trim(rng)) - Application.Find("-", Trim(rng)))
End If
Next

End Sub


exctracts numbers in col A to col B.

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long
Dim pos As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow 'Lastrow to 1 Step -1

pos = InStr(Cells(i, "A").Value2, "-")
If pos > 0 Then

.Cells(i, "B").Value = Right(.Cells(i, "A").Value2, Len(.Cells(i, "A").Value2) - pos)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[frank_m]

Thanks xld, that works a treat

Thank you also mancubus, looks nice, I'll be sure to test it out by tomorrow, , right now I have to get some rest.