CODEWITHSUNDEEP
excelvba
wanderergirl
wanderergirl

Reputation: 13

Excel: Convert Range to Numbers

I am new to Excel VBA coding. I have data that look like this 12,14,18,20-25,27,30 but I would like to make to be 12,14,18,20,21,22,23,24,25,27,30.

In simple it will be to change the range 20-25 to 20,21,22,23,24,25.

Is there a way in excel to do it? Thanks in advance

Upvotes: 0

Views: 254

Answers (1)

user3514930
user3514930

Reputation: 1717

Considering that you have the value in a string:

Sub asdadas()
    MsgBox SplitStr("12,14,18,20-25,27,30,34-47")
End Sub

Function SplitStr(xx As String) As String
    valu = Split(xx, ",")
    Stri = ""
    For Each nn In valu
        If InStr(1, nn, "-") > 0 Then
            For i = Left(nn, InStr(1, nn, "-") - 1) To Right(nn, Len(nn) - InStr(1, nn, "-"))
                If Stri <> "" Then
                    Stri = Stri & "," & i
                Else
                    Stri = i
                End If
            Next
        Else
            If Stri <> "" Then
                Stri = Stri & "," & Trim(nn)
            Else
                Stri = Trim(nn)
            End If
        End If
    Next
    SplitStr = Stri
End Function

Upvotes: 1

Related Questions