MS Excel 2003: Filter final series of numbers in hyperlink address
This Excel tutorial explains how to write a macro to filter a final series of numbers in a hyperlink address in Excel 2003 and older versions (with screenshots and step-by-step instructions).
Question: I've downloaded a table from the web with hyperlink references into Microsoft Excel 2003/XP/2000/97. 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)
Advertisements