Home » Forums » Open Source Learning Center » Members Show and Learn Forum

Needs Help - Extracting numerics from a variable string

Submitted by Dasher on 7 May, 2012 - 07:27

Hi all the Gents and Wiz of Excel VBA, please lend me your genius minds.. I want a funciton or a code in VBA Excel to extract numeric from a given entries. Note that the length and the number of characters and numbers and the placement of " - " are variables. For Example

For LKDF-832J43-FI8329-7834 would yield would yield 83243-8329-7834
V93-394SKJF-24FH89UE77-FSD8 would yield 93-394-248977-8
9W248DFJ349-349-83K38-11 would yield 9248349-349-8338-11

Any help? Thanks Wizards

‹ Returning Multiple Values as Dropdown List Excel macro to adjust negative value with positive value on a given criteria ›
»
Vishesh's picture

In cell A1 put any of your

Submitted by Vishesh on 7 May, 2012 - 08:08.

In cell A1 put any of your entries like LKDF-832J43-FI8329-7834 and in cell B1 put the formula =ExtractNumbers(A1)

To make it work put the following code in a general module

Function ExtractNumbers(str As String) As String

    Dim strMod      As String

    Dim x           As Long

    

    For x = 1 To Len(str)

        Select Case Asc(Mid(str, x, 1))

        Case Asc("a") To Asc("z"), Asc("A") To Asc("Z")

        Case Else

            If Not (Mid(str, x, 1) = "-" And strMod = "") Then

                strMod = strMod & Mid(str, x, 1)

            End If

        End Select

    Next x

    ExtractNumbers = strMod

End Function

 
»

generic

Submitted by aLowlyApprentice on 7 May, 2013 - 03:42.

i made it a little more generic and it satisfied my need. i think it will extract all numerics from a string: 

Function ExtractNumbers(str As String) As String

Dim strMod As String 

Dim x As Long 

Dim ASCII As Integer 



For x = 1 To Len(str) 
ASCII = Asc(Mid(str, x, 1)) 

If 47 < ASCII And ASCII < 58 Then 
strMod = strMod & Mid(str, x, 1) 
End If 
Next x 


ExtractNumbers = strMod





End Function
 
»

Thanks Vishesh

Submitted by Dasher on 7 May, 2012 - 09:59.

Many Thanks, Vishesh... Your geniusness shines happiness on this soul :).. Many thanks, bro.. Cheers

Your code would go a long way. Basically, what you provided is the core of most common data extraction purposes for data transformation. From this core, I could do modifications as the needs demand. But this is just great. I wish I had your expertise..

Many Thanks Vishesh.. You're the go-to guy!

»

Extracting Numerics from strings

Submitted by DavidGonzalesMetrics on 19 January, 2014 - 07:04.

Here is code that uses the function IsNumeric and a literal "-" to parse the string.

' Extract Numbers and dash '-' from cell

Function ExtractNumbers(data)
Dim ch, temp As String
Dim iIndex As Byte

' Initialize variable
temp = ""

' Loop string
For iIndex = 1 To Len(data)

ch = Mid(data, iIndex, 1)

If IsNumeric(ch) Then
temp = temp & ch
End If

If ch = "-" Then
temp = temp & ch
End If

Next iIndex

' Remove leading -
If Left(temp, 1) = "-" Then temp = Mid(temp, 2)

' Remove trailng -
If Right(temp, 1) = "-" Then
temp = Left(temp, Len(temp) - 1)
End If

' Return numbers
ExtractNumbers = temp

End Function

»