Extract Value from Text

[Romulo Avila]

Good afternoon,


I need some help, I have a very long list and I need to extract some numbers from each line, but there is no pattern in the position of these numbers, in some cases they are preceded by the letter N and in other cases this letter does not exist.

** These numbers can range from 33 to 50
27585

RomuloRDM

[Paul_Hossler]

This is probably the easiest way



Option Explicit


Function ExtractNumber(s As String) As Long
Dim v As Variant
Dim i As Long

ExtractNumber = 0


v = Split(s, " ")

For i = LBound(v) To UBound(v)
If v(i) Like "##" Then
ExtractNumber = v(i)
Exit Function

ElseIf v(i) Like "N##" Then
ExtractNumber = Right(v(i), 2)
Exit Function
End If
Next i


End Function

[Romulo Avila]

Paul_Hossler


Good afternoon, thank you very much for your help.

[snb]

This UDF suffices:


Function F_snb(c00)
F_snb=replace(split(c00)(ubound(split(c00))-1),"N","")
End Function