# Thread: [RESOLVED] Converting a string range of number in a string to individual integers

1. ## [RESOLVED] Converting a string range of number in a string to individual integers

So i'm sure this is a simple one but i am having a brain fart. What would be the best way to take a range of numbers stored in a string (e.g 1-10) and convert that in code so the output is 1, 2, 3, 4... etc.

2. ## Re: Converting a string range of number in a string to individual integers

A For .. Next loop?
Code:
```Dim lStartValue As Long, lEndValue As Long
Dim i As Long

' Just some made up lines
lStartValue = CLng(Mid\$(sText, 3, 6))
lEndValue = CLng(Mid\$(sText, 10, 6))

For i = lStartValue To lEndValue
Debug.Print i
Next i```

3. ## Re: Converting a string range of number in a string to individual integers

Not really sure, what you want in the end (an Array or a String-Representation) -
but since we have the Join-Function, you can have "both" basically...

Code:
```Option Explicit

Private Sub Form_Load()
Debug.Print Join(ArrFromRangeStr("1-10"), ", ")
End Sub

Function ArrFromRangeStr(sRange\$, Optional RangeDelim\$ = "-") As Variant()
Dim Arr(), i As Long
ReDim Arr(Split(sRange, RangeDelim)(0) To Split(sRange, RangeDelim)(1))
For i = LBound(Arr) To UBound(Arr): Arr(i) = i: Next
ArrFromRangeStr = Arr
End Function```
HTH

Olaf

4. ## Re: Converting a string range of number in a string to individual integers

have a look at the SPLIT and JOIN functions

5. ## Re: Converting a string range of number in a string to individual integers

Looks like a Printing-Dialogue.
Like: "Print Pages 1-10"
Topic for parsing strings.
If it's really a printing-dialogue you might want to have a look at tha fact, that most programs (Adobe Reader etc.) have the option like
"Print Pages: 1-10;15;20-23;34;46"
So you would have to define a hierarchy of delimiters (in the example the ";" being the topmost)
Algorithm (for my example):
Prepare a not dimensioned FinalResultArray (which you're going to increase with ReDim Preserve)

Split the string along the ";"-character
Run through the resulting array and test each element if it's numeric.
If yes, it's a single page. All is well. Add it to the FinalResultArray
If not, it's a range. Split that element along the next delimiter (in my example the "-"-character). As mentioned above, with a for next-loop you can "fill in" the missing values. Add those values to the FinalResultArray.

In my Example your FinalResultArray would look like
1
2
3
4
5
6
7
8
9
10
15
20
21
22
23
34
46

Or using a Join as last command strResult = Join(FinalResultArray, ", ")
--> "1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 20, 21, 22, 23, 34, 46"

6. ## Re: Converting a string range of number in a string to individual integers

Originally Posted by Schmidt
Not really sure, what you want in the end (an Array or a String-Representation) -
but since we have the Join-Function, you can have "both" basically...

Code:
```Option Explicit

Private Sub Form_Load()
Debug.Print Join(ArrFromRangeStr("1-10"), ", ")
End Sub

Function ArrFromRangeStr(sRange\$, Optional RangeDelim\$ = "-") As Variant()
Dim Arr(), i As Long
ReDim Arr(Split(sRange, RangeDelim)(0) To Split(sRange, RangeDelim)(1))
For i = LBound(Arr) To UBound(Arr): Arr(i) = i: Next
ArrFromRangeStr = Arr
End Function```
HTH

Olaf
Thanks, this was exactly what i needed to point me in the right dimension. I am pulling the data so each range within the text string will become a separate row within the spreadsheet being produced.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured

Click Here to Expand Forum to Full Width