Retrieve Numbers from Mixed Text

This function extracts and returns numbers from text that is a mix of numbers and letters, as shown in Figure 4.11.

RetrieveNumbers (Rng) The argument is myString—The text containing the numbers to be extracted.

Function example:

Function RetrieveNumbers(myString As String) Dim i As Integer, j As Integer Dim OnlyNums As String

'starting at the END of the string and moving backwards (Step -1) For i = Len(myString) To 1 Step -1

'IsNumeric is a VBA function that returns True if a variable is a number 'When a number is found, it is added to the OnlyNums string If IsNumeric(Mid(myString, i, 1)) Then j = j + 1

OnlyNums = Mid(myString, i, 1) & OnlyNums End If

If j = 1 Then OnlyNums = CInt(Mid(OnlyNums, 1, 1)) Next i

RetrieveNumbers = CLng(OnlyNums) End Function

Figure 4.11

Extract numbers from mixed text.

0 0

Post a comment