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

 

[VoG]

I may be missing something but wouldn't this work?

Function Nums(rng As Range) As String
Nums = Replace(rng.Value, "-", ", ")
End Function

Edit: in fact, why a UDF at all

=SUBSTITUTE(A1,"-",", ")

 

[shg]

This is a little long ...

Option Explicit
 
Function Nums(sInp As String) As String
    Dim i           As Long
    Dim j           As Long
    Dim avs1        As Variant
    Dim avs2        As Variant
 
    avs1 = Split(PadNums(Replace(sInp, " ", ""), 3), ",")
 
    For i = 0 To UBound(avs1)
        If InStr(avs1(i), "-") Then
            avs2 = Split(avs1(i), "-")
 
            For j = CLng(Right(avs2(0), 3)) To CLng(Right(avs2(1), 3))
                Nums = Nums & Left(avs2(0), Len(avs2(0)) - 3) & j & ", "
            Next j
 
        Else
            Nums = Nums & avs1(i) & ", "
        End If
    Next i
 
    Nums = PadNums(Left(Nums, Len(Nums) - 2))
End Function
 
Function PadNums(sInp As String, Optional ByVal iLen As Long = 1) As String
    ' shg 2003-1115
    ' Expands numbers in a string to iLen characters for sorting; e.g.,
    '   PadNums("13A1U3", 2)    = "13A01A03"
    '   PadNums("1.2.3.15", 3)  = "001.002.003.015"
 
    ' Numbers are not shortened below their minimal representation:
    '   PadNums("1.123.2.3", 2) = "01.123.02.03"
 
    ' Returns unpadded values if iLen omitted
    '   PadNums("01.123.02.03") = "1.123.2.3"
 
    ' All non-numeric characters are returned as-is
 
    Dim sFmt    As String
    Dim iChr    As Long
    Dim iNum    As Long
    Dim sChr    As String
    Dim bNum    As Boolean
 
    sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0")
 
    For iChr = 1 To Len(sInp) + 1
        sChr = Mid(sInp, iChr, 1)
        If sChr Like "#" Then
            bNum = True
            iNum = iNum * 10 + CLng(sChr)
        Else
            If bNum Then
                bNum = False
                PadNums = PadNums & Format(iNum, sFmt)
                iNum = 0
            End If
            PadNums = PadNums & sChr
        End If
    Next
End Function

 

[Armando Montes]

You might try Find and Replace

 

[shg]

R1, R2, R3-R5, R30 => R1, R2, R3, R4, R5, R30

Find and replace doesn't do that.

 

[VoG]

Yes it does if you specify the correct parameters for the replace (i.e part only).

 

[shg]

True. I'm sure that would be an equally useful solution.

 

[rmullins]

This appears to work perfectly. Thanks so much.

 

[rmullins]

This only replaces the dash with a comma. R1-R4 needs to be converted to R1, R2, R3, R4.

The Macro from SHG4421 appears to fit the bill but thanks anyway.

 

[Valley]

R1, R2, R3-R5, R30 => R1, R2, R3, R4, R5, R30

Find and replace doesn't do that.

Can anybody help give solution? Thanks in advance!

BR//Valley