Notice: WC_Cart::get_cart_url is deprecated since version 2.5! Use wc_get_cart_url instead. in /home/swatipaliwal/public_html/excelcrazy.com/wp-includes/functions.php on line 3839
Excel Crazy

EXTRACT NUMBER FROM STRING – UDF

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
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 *

top
 

© LOKESH PALIWAL 1987 - 2017

Reporting Solutions | Data Consulting | Analytics Training