One of my student asked me this question in this weekend class, how can we extract characters from mix of string and number text.
From text “TM12ETY”, the program/function should return “12” here.
Case 1: If your text is without special characters
‘Below User Define Function Extract Number from string
Case 2: Taking care of special characters
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)
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