# EXTRACT NUMBER FROM STRING – UDF

Share Now

#### PROBLEM STATEMENT

One of my student asked me this question in this weekend class, how can we extract characters from mix of string and number text.

For Example:
From text “TM12ETY”, the program/function should return “12” here.

#### SOLUTION

Case 1: If your text is without special characters

‘Below User Define Function Extract Number from string

` Function extract (txt As String) As String Dim t As String Dim i As Integer Dim noc As Integer extract = "" For i = 1 To noc t = Mid(txt, i, 1) If IsNumeric(t) <> True Then extract = extract & t End If Next i End Function `

Case 2: Taking care of special characters

` Function ExtractNumber (rng As Range) Dim i As Integer For i = 1 To Len(rng) Select Case Asc(Mid(rng.Value, i, 1)) Case 65 To 122 ExtractNumber = ExtractNumber & Mid(rng.Value, i, 1) End Select Next i End Function `

#### APPROACH

When using VBA to scan a text for number, the basic approach is like this:
1. Read each character in a given text.
2. See if it is number using ASCII values, If so, extract it.
3. Hold the number in variable.
4. Continue with next character.

#### STEPS TO PERFORM

1. Go to developer Tab else Press Alt + F11
2.Click on Visual Basic icon
3.Go to Insert click on Module 4.Paste below code in standard module