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

User defined function in excel

4.Paste below code in standard module

CODE_UDF


Share Now
December 21, 2017

0 responses on "EXTRACT NUMBER FROM STRING - UDF"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy

    Drop us a Query

    Call Us: +91 8826547882

    Drop us a Query