Finds location of 1st numeric character inside string starting from a certain character.
It looks stupid to search for all number characters 1 through 0, but this will be faster in large strings (~12mb large)
It utilizes the usage of WorksheetFunction, Min function.
Function VBInstr_Numeric(InString, Optional ByVal StartFromChar = 1)
' Finds the location of 1st numeric character inside string
' any of these characters 1234567890
'
' Needs VBInstr(), WorksheetFunction.Min()
N1 = VBInstr("1", InString, StartFromChar)
N2 = VBInstr("2", InString, StartFromChar)
N3 = VBInstr("3", InString, StartFromChar)
N4 = VBInstr("4", InString, StartFromChar)
N5 = VBInstr("5", InString, StartFromChar)
N6 = VBInstr("6", InString, StartFromChar)
N7 = VBInstr("7", InString, StartFromChar)
N8 = VBInstr("8", InString, StartFromChar)
N9 = VBInstr("9", InString, StartFromChar)
N0 = VBInstr("0", InString, StartFromChar)
N1 = IIf(N1 > 0, N1, Len(InString))
N2 = IIf(N2 > 0, N2, Len(InString))
N3 = IIf(N3 > 0, N3, Len(InString))
N4 = IIf(N4 > 0, N4, Len(InString))
N5 = IIf(N5 > 0, N5, Len(InString))
N6 = IIf(N6 > 0, N6, Len(InString))
N7 = IIf(N7 > 0, N7, Len(InString))
N8 = IIf(N8 > 0, N8, Len(InString))
N9 = IIf(N9 > 0, N9, Len(InString))
N0 = IIf(N0 > 0, N0, Len(InString))
FirstNumber = WorksheetFunction.Min(N1, N2, N3, N4, N5, N6, N7, N8, N9, N0)
Rett = IIf(FirstNumber = Len(InString), 0, FirstNumber)
VBInstr_Numeric = Rett
End Function
' Finds the location of 1st numeric character inside string
' any of these characters 1234567890
'
' Needs VBInstr(), WorksheetFunction.Min()
N1 = VBInstr("1", InString, StartFromChar)
N2 = VBInstr("2", InString, StartFromChar)
N3 = VBInstr("3", InString, StartFromChar)
N4 = VBInstr("4", InString, StartFromChar)
N5 = VBInstr("5", InString, StartFromChar)
N6 = VBInstr("6", InString, StartFromChar)
N7 = VBInstr("7", InString, StartFromChar)
N8 = VBInstr("8", InString, StartFromChar)
N9 = VBInstr("9", InString, StartFromChar)
N0 = VBInstr("0", InString, StartFromChar)
N1 = IIf(N1 > 0, N1, Len(InString))
N2 = IIf(N2 > 0, N2, Len(InString))
N3 = IIf(N3 > 0, N3, Len(InString))
N4 = IIf(N4 > 0, N4, Len(InString))
N5 = IIf(N5 > 0, N5, Len(InString))
N6 = IIf(N6 > 0, N6, Len(InString))
N7 = IIf(N7 > 0, N7, Len(InString))
N8 = IIf(N8 > 0, N8, Len(InString))
N9 = IIf(N9 > 0, N9, Len(InString))
N0 = IIf(N0 > 0, N0, Len(InString))
FirstNumber = WorksheetFunction.Min(N1, N2, N3, N4, N5, N6, N7, N8, N9, N0)
Rett = IIf(FirstNumber = Len(InString), 0, FirstNumber)
VBInstr_Numeric = Rett
End Function
InString, Optional ByVal StartFromChar = 1
Views 192
Downloads 44
CodeID
DB ID