Custom Function coding

manster_mg · Feb 16th 2018

I have a custom function created to extract integers from a string. This initially worked fine when the strings consisted of a set format of text+integer. Now I have instances where the string consists of integers+text+integers but I only want to capture the first set of integers. I've copied the code for the function below and I'm looking for a way to modify so I can extract the first set of integers from the string.
I've also attached a workbook as an example.

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

gijsmo · Feb 19th 2018

Based on the sample provided, either of the following functions should extract the leftmost number from the string, starting at position 1:

Code

Function GetFirstNum(MyInput As String)
  Dim j As Integer


  For j = 1 To Len(MyInput)
    If IsNumeric(Mid(MyInput, j, 1)) Then
      GetFirstNum = GetFirstNum & Mid(MyInput, j, 1)
    Else
      Exit For
    End If
  Next
End Function




Function GetFirstNumWithDelim(MyInput As String)
  Dim j As Integer


  j = InStr(MyInput, "|")
  If j > 0 Then
    GetFirstNumWithDelim = Trim(Left(MyInput, j - 1))
  End If
End Function

Display More

The 2nd function uses the fact that all your samples contained the same "|" delimiter after the initial number.

manster_mg · Feb 19th 2018

The 2nd function works nicely for my purposes. Thank you very much gijsmo!!

Custom Function coding

Participate now!

Share