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
November 15, 2019

0 responses on "EXTRACT NUMBER FROM STRING - UDF"

    Leave a Message

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

    EC Analytics will help your business make better decisions by providing expert-level business intelligence (BI) services. Forecasting, strategy, optimization, performance analysis, trend analysis, customer analysis, budget planning, financial reporting and more. EC Analytics also offers Advanced Data Analytics training in corporate and retail.

    Social Media

    ECAnalytics-facebookECAnalytics_Linkedin

    Address:

    NM 23, SECTOR 14, OLD DLF COLONY,
    GURGAON (HARYANA)
    0124- 4601426

    EC Analytics Consulting @ 2019 ALL RIGHTS RESERVED