Parse AlphaNumeric String

[drmingle]

Objective: To create a list of numbers based a cell which are present in an alpha-numeric string

Issues: "-" shows up at times without the context of numbers (i.e. MS-DRG)
Issues: I am not interested in float numbers (i.e. 37.63-37.66)

'Example data in cells (row 1 = A1, row 2 =A2, row 3 = A3, row 4 = A4)
MS-DRGs 767-768, 774-775
Rev 115, 125, 135, 145, 155, 655-656
Rev 174
MS-DRG 001 with ICD-9 37.63-37.66, 37.52

'Desired Results (row 1 = B1, row 2 =B2, row 3 = B3, row 4 = B4)
767,767,768,774,775
115, 125, 135, 145, 155, 655, 656
174
001

Any help would be appreciated.

 

[drmingle]

Give this macro a try...

Sub GetNumericRanges()
  Dim X As Long, Y As Long, Z As Long, LastRow As Long, FirstDigit As Long, FirstLetter As Long
  Dim Pages As String, Numbers() As String, sRange() As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = 1 To LastRow
    Pages = Cells(X, "A").Value
    FirstDigit = Evaluate("MIN(FIND(ROW(1:10)-1,""" & Pages & """&""0123456789""))")
    For Z = FirstDigit To Len(Pages) + 1
      If Mid(Pages & "x", Z, 1) Like "[a-z]" Then
        FirstLetter = Z
        Exit For
      End If
    Next
    Pages = Replace(Mid(Pages, FirstDigit, FirstLetter - FirstDigit), " ", "")
    Numbers = Split(Pages, ",")
    For Z = 0 To UBound(Numbers)
      If Numbers(Z) Like "*-*" Then
        sRange = Split(Numbers(Z), "-")
        Numbers(Z) = ""
        For Y = sRange(0) To sRange(1)
          Numbers(Z) = Numbers(Z) & "," & Y
        Next
        Numbers(Z) = Mid(Numbers(Z), 2)
      End If
    Next
    Cells(X, "B").Value = "'" & Join(Numbers, ", ")
  Next
End Sub

Rick -

This worked perfectly...Fantastic job.

 

[drmingle]

my code excludes numbers containing a decimal point. as for numbers between the bookmarks of the
dash, that wasn't requested in the original post.

JoeMo -

Thank you as well for your solution and for responding so quickly...