How to custom function that was created to extract integers from a simple 11 character string

How to create a custom function to extract integers from a simple 11 character string

Category: [Excel] Demo Available

How to create a custom function to extract integers from a simple 11 character string

Requirement:

The user has a custom function that was created to extract integers from a simple 11 character string that was alphanumeric with a hyphen.

The user needs to modify or create a new custom function that can extract integers (with hyphens in some instances). The new string is variable in length and the integers the user needs to extract are on the left side of a vertical slash (vertical bar).

The custom function the user is currently using is posted below:

Code:

Function GetNum(MyInput As String)
Dim i As Integer
Dim j As Integer
Dim NumOnly As String
'Count total strings of Input
    For i = Len(MyInput) To 1 Step -1
     If IsNumeric(Mid(MyInput, i, 1)) Then
     j = j + 1
     NumOnly = Mid(MyInput, i, 1) & NumOnly
     End If
     
     If j = 1 Then NumOnly = CInt(Mid(NumOnly, 1, 1))
     Next i
     
     GetNum = NumOnly

End Function

Here is an example of the old string and new string:
Old String: ABC-1234567
New string 1: 1234567-99 | TEXT MORETEXT EXTENDEDTEXT - 23.8 XX YYYY 16:9
New string 2: 1234567 | BLAH BLAH EXTENDEDBLAH XYZ 123

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1202952-custom-function-coding

Solution:

To keep the hyphen there are 2 options.

If you expect characters other than numbers and hyphens to the left of the vertical pipe symbol then this should work:

Code:

Function ExtractNumA(MyInput As String) As String
  Dim j As Integer, k As Integer
  Dim c As String
  
  ExtractNumA = ""
  j = InStr(MyInput, "|")
  If j = 0 Then Exit Function
  
  For k = 1 To j - 1
    c = Mid(MyInput, k, 1)
    If IsNumeric(c) Or c = "-" Then ExtractNumA = ExtractNumA & c
  Next
End Function

If there are only numbers and hyphens to the left of the vertical pipe symbol then this should also work and would be quicker:

Code:

Function ExtractNumB(MyInput As String) As String
  Dim j As Integer, k As Integer
  Dim c As String
  
  ExtractNumB = ""
  j = InStr(MyInput, "|")
  If j = 0 Then Exit Function
  
  ExtractNumB = Left(MyInput, j - 1)
End Function

In both cases all hyphen characters will be returned eg, if there is more than one

Obtained from the OzGrid Help Forum.

Solution provided by gijsmo.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.