Extracting Numbers from Strings on Excel

Here I'll start to share some functions that I developed for Visual Basic on Excel some years ago. They all include explanatory comments in english, but the names of variables are in spanish.

This function delivers a number as double, but it can be easily modified to take it as string just erasing the last line and changing the function type to Double.

'This function extracts a numerical string from

'another string that may contain other characters

'it only extract the FIRST numerical string

'obviously, it can be pointed to a cell that contains text

'example: EXTRAERNUM("sampletext853first")=853

'example: EXTRAERNUM("sample16text853first")=16

'%Copyright, 2007, Andrés González

Function EXTRAERNUM(texto As String) As Double

Dim buscando, caracter, posCar, inicioNumero, finalNumero

buscando = "primera"

For posCar = 1 To Len(texto)

caracter = Mid(texto, posCar, 1)

If (IsNumeric(caracter) = True And buscando = "primera") Then

inicioNumero = posCar

finalNumero = posCar

buscando = "ultima"

ElseIf (IsNumeric(caracter) = True And buscando = "ultima") Then

finalNumero = posCar

ElseIf (IsNumeric(caracter) = False And buscando = "ultima") Then

buscando = "nada"

End If

Next posCar

EXTRAERNUM = Mid(texto, inicioNumero, finalNumero - inicioNumero + 1)

EXTRAERNUM = Val(EXTRAERNUM)

End Function