# Removing Dash from text number string

This is a discussion on Removing Dash from text number string within the Excel Questions forums, part of the Question Forums category; We often get spreadsheets from our customer that are formatted with both comma and dash combinations. We would like to ...

1. ## Removing Dash from text number string

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(n), "-") Then
For adnum = Split(num(n), "-")(0) To Split(num(n), "-")(1)
Txt = Txt & adnum & ","
Else
Txt = Txt & num(n) & ","
End If
Next n
Nums = Txt
End Function

2. ## Re: Removing Dash from text number string

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

Code:
```Function Nums(rng As Range) As String
Nums = Replace(rng.Value, "-", ", ")
End Function```
Edit: in fact, why a UDF at all

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

3. ## Re: Removing Dash from text number string

This is a little long ...

Code:
```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.,

' Numbers are not shortened below their minimal representation:

' Returns unpadded values if iLen omitted

' 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
iNum = 0
End If
End If
Next
End Function```

4. ## Re: Removing Dash from text number string

You might try Find and Replace

5. ## Re: Removing Dash from text number string

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

Find and replace doesn't do that.

6. ## Re: Removing Dash from text number string

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

7. ## Re: Removing Dash from text number string

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

8. ## Re: Removing Dash from text number string

This appears to work perfectly. Thanks so much.

9. ## Re: Removing Dash from text number string

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.

10. ## Re: Removing Dash from text number string

Originally Posted by shg
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

Page 1 of 2 12 Last