Custom Function coding

  • 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.

    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


  • 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:

  • 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?

  • 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:

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

    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

  • 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:

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

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!