Spreadsheet 123 Excel 2003, XP, 2000, 97 FAQs: Filter Hyperlinks

Question:  I've downloaded a table from the web with hyperlink references into Excel. I need to filter out the final series of numbers for each hyperlink address and put them as a number in a column. How can I do this?

Answer:  Below is a function that you can include in your spreadsheet to filter the final series of numbers in a hyperlink address in Excel.

Function FinalNumberSeries(pCell) As Long

Dim LChar As String
Dim LLength As Integer
Dim LStart As Integer
Dim LExit As Boolean

LLength = Len(pCell)
LStart = LLength
LExit = False

'Search backwards through the string until a non-numeric
'character is found
Do Until LExit = True
   LChar = Mid(pCell, LStart, 1)

   If IsNumeric(LChar) = False Then
      LExit = True
   ElseIf LStart = 1 Then
      LExit = True
End If

LStart = LStart - 1
Loop

FinalNumberSeries = Mid(pCell, LStart + 2)

End Function

Then you can reference this new function in your spreadsheet.
For example in cell B1, you could enter the following:

=FinalNumberSeries(A1)