Page 1 of 2 12 LastLast
Results 1 to 10 of 12

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. #1
    New Member
    Join Date
    Oct 2004
    Location
    Boise,Id
    Posts
    3

    Default 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 & ","
    Next adnum
    Else
    Txt = Txt & num(n) & ","
    End If
    Next n
    Nums = Txt
    End Function

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,472

    Default 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,"-",", ")
    Last edited by VoG; Oct 3rd, 2010 at 12:38 PM.
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,537

    Default 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.,
        '   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

  4. #4
    Board Regular
    Join Date
    Sep 2008
    Posts
    505

    Default Re: Removing Dash from text number string

    You might try Find and Replace

  5. #5
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,537

    Default 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. #6
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,472

    Default Re: Removing Dash from text number string

    Yes it does if you specify the correct parameters for the replace (i.e part only).
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,537

    Default Re: Removing Dash from text number string

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

  8. #8
    New Member
    Join Date
    Oct 2004
    Location
    Boise,Id
    Posts
    3

    Default Re: Removing Dash from text number string

    This appears to work perfectly. Thanks so much.

  9. #9
    New Member
    Join Date
    Oct 2004
    Location
    Boise,Id
    Posts
    3

    Default 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. #10
    Board Regular
    Join Date
    Aug 2006
    Posts
    57

    Default Re: Removing Dash from text number string

    Quote Originally Posted by shg View Post
    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 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com