Displaying a string in a different format

I need to format a string and display the results in a list box.  A couple of string examples would be:

34030089 U110-115
100-0714-001 R113-114

I need it to be displayed in a list box, in this format:

34030089 U110
34030089 U111
34030089 U112
34030089 U113
34030089 U114
34030089 U115
100-0714-001 R113
100-0714-001 R114

I have multiple strings to work with and I can't seem to be able to get the desired information accurately.
rroundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
StapleheadCommented:
rround,

the following subroutine does what you want...

keep in mind, though, that there are some assumptions:
1) there's no error checking that the input string is
   well-formed (always an alpha before the number, always
   a dash for a range of numbers, etc)

2) i fill the listbox directly from the routine.

3) i don't check that the first number in the range is
   less than or equal to the second.

let me know if you need more comments; here it is...

Public Sub ExpandNumbers(lstrSerial As String, ctlList As ListBox)

Dim Finished As Boolean
Dim iDashPosition As Integer
Dim iNumberPosition As Integer
Dim sTemp As String

Dim i As Integer
Dim sPrefix As String

Dim iLowBound As Integer
Dim iHighBound As Integer

'initialize temp variables
Finished = False
iDashPosition = Len(lstrSerial)

'search for the right-most dash character
While Not Finished
    If Mid(lstrSerial, iDashPosition, 1) = "-" Then
        Finished = True
    Else
        iDashPosition = iDashPosition - 1
    End If
Wend

'find the position of the letter which precedes
'the number (e.g., "34030089 U110-115")
iNumberPosition = iDashPosition - 1
Finished = False
While Not Finished
    sTemp = Mid(lstrSerial, iNumberPosition, 1)
    If sTemp < "0" Or sTemp > "9" Then
        Finished = True
    Else
        iNumberPosition = iNumberPosition - 1
    End If
Wend

iLowBound = CInt(Mid(lstrSerial, iNumberPosition + 1, iDashPosition - iNumberPosition - 1))
iHighBound = CInt(Mid(lstrSerial, iDashPosition + 1, Len(lstrSerial) - iDashPosition))

'set up the strings' prefixes...
sPrefix = Left(lstrSerial, iNumberPosition)

For i = iLowBound To iHighBound
    sTemp = sPrefix & CStr(i)
    ctlList.AddItem (sTemp)
Next i

End Sub
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
rroundAuthor Commented:
This works great, however, I also need the code to detect this type of string:

102-0618-001 R12-18,R126-131,R140

and have it display in the same format as the other strings.


0
 
StapleheadCommented:
you can add one of two things to the code:
1) some parsing that looks for comma-delimited ranges, then passes each separately to
       the ExpandNumbers routine,
or
2) code in ExpandNumbers itself, which looks for dash *or* comma, treating each separately.
       the problem here is, how do you know when a dash is in the prefix or in a range?
       you'd be stuck assuming that the alpha that precedes will *always* be there...

Larry
0
 
rroundAuthor Commented:
Sorry about the delay in responding.  The code and advice you gave worked great. Thank you.

Rob.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.