Parsing hyphenated number ranges in a field

[pete171]

Hello,

Does anyone know of a function or macro that can parse out hyphenated number ranges in a field.

For example, in cell A1 we have the following data:

84.00-84.19, 84.91

Would like to data to be changed to the following in cell A1:

84.00, 84.01, 84.02, 84.03, 84.04, 84.05, 84.06, 84.07, 84.08, 84.09, 84.10, 84.11, 84.12, 84.13, 84.14, 84.15, 84.16, 84.17, 84.18, 84.19, 84.91

Thx!
Pete

[MickG]

Hi Pete,
This Code will overwrite you data in "A1"
You could turn it into a function if required.

Dim Sdata, Ray, Oct, EData, Rng, Num As Single, oStr
Ray = Split([a1], ",")
For Oct = 0 To UBound(Ray)
    If InStr(Ray(Oct), "-") Then
        Sdata = Left(Ray(Oct), InStr([a1], "-") - 1)
        EData = Right(Ray(Oct), Len(Ray(Oct)) - (Len(Sdata) + 1))

        For Num = Val(Sdata) To Val(EData) + 0.01 Step 0.01
                oStr = oStr & Format(Num, "0.00") & ","
             Next Num
    Else
         oStr = oStr & Ray(Oct)
     End If
Next Oct
Range("A1") = oStr

Regards Mick

[pete171]

Thanks Mick.

That works perfectly for the example data. However when I run it on the following combination I don't get the desired result.

Values in cell A1 before:

35.00-35.04, 35.10-35.14, 35.20-35.28, 35.31-35.35, 35.39, 35.42, 35.50-35.51, 35.53-35.54, 35.60-35.63, 35.70-35.73, 35.81-35.84, 35.91-35.95, 35.98-35.99, 37.10-37.11, 37.24-37.25, 37.31-37.33, 37.35, 37.41, 37.49

Values in cell A1 after:

35.00,35.01,35.02,35.03,35.04,35.05, 35.39 35.42 37.35 37.41 37.49

Desired result should be:

35.00,35.01,35.02,35.03,35.04,35.10,35.11,35.12,35.13,35.14,35.20,35.21,35.22,35.23,35.24,35.25,35.26,35.27,35.28,35.31,35.32,35.33,35.34,35.35,35.39,35.42,35.50,35.51,35.53,35.54,35.60,35.61,35.62,35.63,35.70,35.71,35.72,35.73,35.81,35.82,35.83,35.84,35.91,35.92,35.93,35.94,35.95,35.98,35.99,37.10,37.11,37.24,37.25,37.31,37.32,37.33,37.35,37.41,37.49

Cheers
Pete

[MickG]

Hi Pete, Try this, it's seems a bit better.
NB:- The result is in "A2", change range at bottom of code if required.

Dim Sdata As String, Ray, Oct As Integer, EData As String, rng, Num, oStr As String
Dim c As Single
c = "0.01" 
Ray = Split([a1], ",")
For Oct = 0 To UBound(Ray)
    
    If InStr(Ray(Oct), "-") Then
        Sdata = Left(Trim(Ray(Oct)), InStr([a1], "-") - 1)
        EData = Right(Trim(Ray(Oct)), Len(Trim(Ray(Oct))) - (Len(Sdata) + 1))

               For Num = Sdata To EData Step c
                oStr = oStr & Format(Round(Num, "2"), "0.00") & ","
             
             Next Num
    Else
         oStr = oStr & Trim(Ray(Oct)) & ","
    
     End If
'Debug.Print oStr

Next Oct
Range("A2") = oStr

Regards Mick

[mikerickson]

This UDF approach can be used two ways, either call OverwrittingSub to replace the data in A1 or put
=SeparatedNumberIntervals(A1) in a cell.

Sub OverwrittingSub()
    Range("A1").Value = SeparatedNumberIntervals(CStr(Range("A1").Value))
End Sub

Function SeparatedNumberIntervals(inputString As String)
    Dim subStrings As Variant, i As Long
    subStrings = Split(inputString, ",")
    For i = 0 To UBound(subStrings)
        subStrings(i) = IntervalString(subStrings(i))
    Next i
    SeparatedNumberIntervals = Join(subStrings, ", ")
End Function

Private Function IntervalString(aString As Variant) As String
    Dim i As Long, xRRay As Variant
    If aString Like "*#*-*#*" Then
        xRRay = Split(aString, "-")
        For i = 100 * xRRay(0) To 100 * xRRay(1)
            IntervalString = IntervalString & ", " & Format(i / 100, "0.00")
        Next i
        IntervalString = Mid(IntervalString, 3)
    Else
        IntervalString = Format(Val(aString), "0.00")
    End If
End Function

[pete171]

Thanks again Mick.

There are some instances where the "before" values are listed with a non-range number followed by a number range...for example: 29.31, 35.00-35.04, 35.10-35.14, 35.20... instead of 35.00-35.04, 35.10-35.14, 35.20

The code will err out when a range is not listed first. Any suggestions?

regards,
Pete

[MickG]

Hi Pete, try this it should be better.

Dim Sdata As String, Ray, oCt As Integer, EData As String, rng, Num, oStr As String
Dim C As Single
C = "0.01" 

Ray = Split([a1], ",")
For oCt = 0 To UBound(Ray)
    
    If InStr(Ray(oCt), "-") Then
        Sdata = Left(Trim(Ray(oCt)), InStr(Trim(Ray(oCt)), "-") - 1)
        EData = Right(Trim(Ray(oCt)), Len(Trim(Ray(oCt))) - (Len(Sdata) + 1))

               For Num = Sdata To EData Step C
                 oStr = oStr & Format(Round(Num, "2"), "0.00") & ","
                Next Num
    Else
         oStr = oStr & Trim(Ray(oCt)) & ","
    
     End If
'Debug.Print oStr

Next oCt
Range("A2") = Left(oStr, Len(oStr) - 1)

Regards Mick