Removing Dash from text number string

[rmullins]

We often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to only have the comma seperation.

Example:
R1, R2, R3-R5, R30

Result:
R1, R2, R3, R4, R5, R30

There is usually only one alpha character but sometimes more. Example: CR1, CR2, CR3-CR5.

The following macro works great if there are no alpha characters. Anyone have any idea how to solve the alpha/numeric combination?

Function Nums(rng As Range) As String
Dim adnum As Integer, n As Integer, num, Txt As String
num = Split(rng, ",")
For n = 0 To UBound(num)
If InStr(num, "-") Then
For adnum = Split(num, "-")(0) To Split(num, "-")(1)
Txt = Txt & adnum & ","
Next adnum
Else
Txt = Txt & num & ","
End If
Next n
Nums = Txt
End Function

 

[lablover1]

Wouldn't a wildcard character help in the Find/Replace option (I have done that before).

 

[Gonfishn]

You might try Find and Replace

New to forum, needed this specific question answered, read through postings and tried this and it worked perfectly saving me a lot of time in the process. I was removing a dash in a UPC code between the last two digits. Again, thanks folks.