I need to find a quick way of retrieving all the numbers entered by a user.
So if a user for example enters "3-8, 21, 27, 29-33" into a text field I need to extract from this
If another user enters "4, 11, 22-25" I need to get "4,11,22,23,24,25" from this entry so that it can be saved into the database.
Im not VBA expert - so how would this be done in the simplest way.
My thoughts were to :-
1) parse the string checking for the first comma
2) then check for a dash
3) extracting the numbers to the left and right of the dash (if there is one) and inserting into a loop as the min (number to the left of dash)and max (number to the right of dash) values to retrieve all the numbers inbetween to enter into the database.
4) Repeat process until all numbers have been extracted.
Sorry if this doesnt make sense - i am half asleep, and it has been some time since i have programmed and in VBA.
This should work but some verification for invalid characters in the original string list should be added:
Function ParseList(ByVal List As String) As String
Dim var As Variant
Dim i As Long
Dim j As Long
Dim x As Long
Dim y As Long
var = Split(List, ",")
For i = 0 To UBound(var)
var(i) = Trim(var(i))
If InStr(var(i), "-") > 1 Then
x = Left(var(i), InStr(var(i), "-") - 1)
y = Mid(var(i), InStr(var(i), "-") + 1)
var(i) = ""
For j = x To y
If Len(var(i)) > 0 Then var(i) = var(i) & ","
var(i) = var(i) & CStr(j)
If Len(ParseList) > 0 Then ParseList = ParseList & ","
ParseList = ParseList & var(i)