Ok so let's say your imported data just has one field I'd create an additional field in my table to store the 'final' string
Code:
tblTest
ImportString FinalString
C20-21,C30-35,C44
Then run code like this:
Code:
Dim db As Database
Dim rst As Recordset
Dim sString As String
Dim aLineArray
Dim sArrayItem As String
Dim sPrefix As String
Dim sSuffix As String
Dim iMin As Integer
Dim iMax As Integer
Dim sFinalString As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT ImportString, FinalString FROM tblTest WHERE (FinalString) Is Null")
If rst.RecordCount <> 0 Then
Do While rst.EOF <> True
'resets the finalstring
sFinalString = ""
'removes any spaces in string
If Len(Trim(rst!importstring)) > 0 Then
sString = Replace(rst!importstring, " ", "")
Else
sString = ""
End If
'if the string length is greater than 0
If Len(sString) > 0 Then
'split the string into an array of items delimited by a comma
aLineArray = Split(sString, ",")
'for each item in the array
For a = 0 To UBound(aLineArray)
'starting array string
sArrayItem = aLineArray(a)
sPrefix = Left(sArrayItem, 1)
'finds the min and max range of any string in the array
If InStr(sArrayItem, "-") > 0 Then
iMin = Mid(sArrayItem, 2, InStr(sArrayItem, "-") - 2)
iMax = Right(sArrayItem, Len(sArrayItem) - InStr(sArrayItem, "-"))
Else
iMin = Right(sArrayItem, Len(sArrayItem) - 1)
iMax = iMin
End If
'cycles through the values within each segment to create a final string
For iMin = iMin To iMax
sFinalString = sFinalString & sPrefix & iMin & ","
Next iMin
'Debug.Print " " & iMin & " " & iMax
Next a
sFinalString = Left(sFinalString, Len(sFinalString) - 1)
'updates the table with the final string
rst.Edit
rst!finalstring = sFinalString
rst.Update
rst.MoveNext
Else
rst.MoveNext
End If
Loop
End If