Custom Function coding

manster_mg · Apr 27th 2018

I have a custom function that was created to extract integers from a simple 11 character string that was alphanumeric with a hyphen. I need 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 I need to extract are on the left side of a vertical slash (vertical bar). The custom function I am currently using is posted below. If any one can help with the modification needed or script new code for the new string I am dealing with I would be very grateful.

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

Display More

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

Cheers!

gijsmo · Apr 29th 2018

It looks like you just want the numeric values to the left of the slash returned as a string (although you have no return type on your custom function but it's returning a string, not a number).

If you want something similar for extracting numbers to the left of the slash try:

Code

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

Display More

manster_mg · Apr 30th 2018

Your code worked fine with one exception, I wanted to capture the hyphen 99 in cases where the string has a -99 on the left side of the slash. Any suggestions?

gijsmo · Apr 30th 2018

OK, 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

Display More

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

manster_mg · May 1st 2018

Thank you gijsmo, I decided to go with the first option as it looks like I will be able to use this for many more instances. I am still a novice to VBA and Excel and I am trying to understand how the code operates. Can you tell me if my observations below are correct?
After Dims are declared:

Code

ExtractNumA = " "
' This sets the dim to a blank string value?)
j = InStr(MyInput, "|") 
' assigns the numeric value for the position of the pipe symbol in MyInput
If j = 0 The Exit Function
' exits if no pipe symbol found
For k = 1 To j - 1
' not sure I understand this statement but I'm guessing this defines all the positions of MyInput from the pipe symbol to the first character in MyInput
  c = Mid(MyInput, k, 1)
' sets dim c to the string in MyInput from position 1 to the pipe symbol
If Isnumeric(c) Or c = "-" Then ExtractNumA = ExtractNumA & c
Next
' The If Then Next statement evaluates the string values in dim c and extract integers or hyphens and adds them to dim ExtractNumA (which was blank)

Display More

Wow, I didn't think I could walk it through but this is how my brain works.
Did I get it right?

gijsmo · May 2nd 2018

Yes, pretty much right...the For ...Next loop basically works as follows:

1. Because we know the location of the pipe symbol, we can loop from position 1 in the input string up to the position just before the pipe symbol in the input string
2. The c = Mid(Myinput, k, 1) just extracts the current character out of the input string based on where we are in the loop
3. It then checks that character to see if it is numeric or a hyphen character, if it is it adds it to the output string

As this is a function, ExtractNumA can be used as the variable to be returned, a string in this instance.
It is actually not necessary to set it to "" (null) because VBA will initialise it to null however it is good coding practice and makes it clear what will be returned if, eg, there is no hyphen character in the input string.

Custom Function coding

Participate now!

Share