none
How to replace ;#<number> from excel sheet RRS feed

  • Question

  • Hi,

    I have a column in excel that contains semicolon followed by hash and then a number. Like: ";#22" . I want to design a macro which will help me replace it with just a comma (,). 

    I'm recording macro and when i do find and replace, i'm unable to select after ;#. That means i can't select numbers to replace. 

    Please help. 

    Regards,

    prajK

    Tuesday, October 6, 2015 10:00 AM

Answers

  • I have a column in excel that contains semicolon followed by hash and then a number. Like: ";#22" . I want to design a macro which will help me replace it with just a comma (,). 

    I'm recording macro and when i do find and replace, i'm unable to select after ;#.

    Option Explicit
    
    Sub Main()
      Const Mask = ";#"
      Dim All As Range, This As Range
      Dim SubStrings As Collection
      Dim SubString
      Dim Content As String
      Dim i As Long, j As Long
      
      'Find all cells that contains the mask
      Set All = FindAll(Cells, Mask, LookAt:=xlPart)
      'Visit each cell
      For Each This In All
        'Step 1: Parse the mask and following numbers
        Content = This.Value
        Set SubStrings = New Collection
        i = InStr(1, Content, Mask)
        j = 0
        Do While i > j
          j = i + 2
          Do While Mid$(Content, j, 1) Like "#"
            j = j + 1
          Loop
          If j > i + 2 Then
            SubString = Mid(Content, i, j - i)
            SubStrings.Add SubString
          End If
          j = i + 2
          i = InStr(j, Content, Mask)
        Loop
        'Step 2: Sort the collection: Longest strings first
        InsertionSortCollection SubStrings
        'Replace all occurences with a comma
        For Each SubString In SubStrings
          This.Replace SubString, ",", xlPart
        Next
      Next
    End Sub
    
    Private Sub InsertionSortCollection(ByRef Liste As Collection)
      Dim i As Long, j As Long, Temp, Doit As Boolean
      For i = 2 To Liste.Count
        Temp = Liste(i)
        For j = i - 1 To 1 Step -1
          If Liste(j) >= Temp Then Exit For
          Doit = True
        Next
        If Doit Then
          Liste.Remove i
          Liste.Add Temp, Before:=j + 1
          Doit = False
        End If
      Next
    End Sub
    
    Private Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where (Windows version)
      Dim FirstAddress As String
      Dim c As Range
      'From FastUnion:
      Dim Stack As New Collection
      Dim Temp() As Range, Item
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set c = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = c.Cells(c.Rows.Count * CDec(c.Columns.Count))
      End If
    
      Set c = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If c Is Nothing Then Exit Function
    
      FirstAddress = c.Address
      Do
        Stack.Add c
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set c = Where.Find(What, c, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set c = Where.FindNext(c)
          Else
            Set c = Where.FindPrevious(c)
          End If
        End If
        'Can happen if we have merged cells
        If c Is Nothing Then Exit Do
      Loop Until FirstAddress = c.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      ReDim Temp(0 To Stack.Count - 1)
      i = 0
      For Each Item In Stack
        Set Temp(i) = Item
        i = i + 1
      Next
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    

    • Marked as answer by John.P.Smith Thursday, October 8, 2015 7:50 AM
    Wednesday, October 7, 2015 10:03 AM
  • Change the line

    If j > i + 2 Then

    to

    If j >= i + 2 Then
    • Marked as answer by John.P.Smith Thursday, October 8, 2015 7:51 AM
    Thursday, October 8, 2015 7:02 AM

All replies

  • Hi prajK,

    This is the forum to discuss questions and feedback for Office 2013, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, October 7, 2015 2:44 AM
  • Thank you Zhang, that makes sense to me. 

    Regards,

    prajK

    Wednesday, October 7, 2015 4:31 AM
  • I have a column in excel that contains semicolon followed by hash and then a number. Like: ";#22" . I want to design a macro which will help me replace it with just a comma (,). 

    I'm recording macro and when i do find and replace, i'm unable to select after ;#.

    Option Explicit
    
    Sub Main()
      Const Mask = ";#"
      Dim All As Range, This As Range
      Dim SubStrings As Collection
      Dim SubString
      Dim Content As String
      Dim i As Long, j As Long
      
      'Find all cells that contains the mask
      Set All = FindAll(Cells, Mask, LookAt:=xlPart)
      'Visit each cell
      For Each This In All
        'Step 1: Parse the mask and following numbers
        Content = This.Value
        Set SubStrings = New Collection
        i = InStr(1, Content, Mask)
        j = 0
        Do While i > j
          j = i + 2
          Do While Mid$(Content, j, 1) Like "#"
            j = j + 1
          Loop
          If j > i + 2 Then
            SubString = Mid(Content, i, j - i)
            SubStrings.Add SubString
          End If
          j = i + 2
          i = InStr(j, Content, Mask)
        Loop
        'Step 2: Sort the collection: Longest strings first
        InsertionSortCollection SubStrings
        'Replace all occurences with a comma
        For Each SubString In SubStrings
          This.Replace SubString, ",", xlPart
        Next
      Next
    End Sub
    
    Private Sub InsertionSortCollection(ByRef Liste As Collection)
      Dim i As Long, j As Long, Temp, Doit As Boolean
      For i = 2 To Liste.Count
        Temp = Liste(i)
        For j = i - 1 To 1 Step -1
          If Liste(j) >= Temp Then Exit For
          Doit = True
        Next
        If Doit Then
          Liste.Remove i
          Liste.Add Temp, Before:=j + 1
          Doit = False
        End If
      Next
    End Sub
    
    Private Function FindAll(ByVal Where As Range, ByVal What, _
        Optional ByVal After As Variant, _
        Optional ByVal LookIn As XlFindLookIn = xlValues, _
        Optional ByVal LookAt As XlLookAt = xlWhole, _
        Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
        Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
        Optional ByVal MatchCase As Boolean = False, _
        Optional ByVal SearchFormat As Boolean = False) As Range
      'Find all occurrences of What in Where (Windows version)
      Dim FirstAddress As String
      Dim c As Range
      'From FastUnion:
      Dim Stack As New Collection
      Dim Temp() As Range, Item
      Dim i As Long, j As Long
    
      If Where Is Nothing Then Exit Function
      If SearchDirection = xlNext And IsMissing(After) Then
        'Set After to the last cell in Where to return the first cell in Where in front if _
          it match What
        Set c = Where.Areas(Where.Areas.Count)
        'BUG in XL2010: Cells.Count produces a RTE 6 if C is the whole sheet
        'Set After = C.Cells(C.Cells.Count)
        Set After = c.Cells(c.Rows.Count * CDec(c.Columns.Count))
      End If
    
      Set c = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
      If c Is Nothing Then Exit Function
    
      FirstAddress = c.Address
      Do
        Stack.Add c
        If SearchFormat Then
          'If you call this function from an UDF and _
            you find only the first cell use this instead
          Set c = Where.Find(What, c, LookIn, LookAt, SearchOrder, _
            SearchDirection, MatchCase, SearchFormat:=SearchFormat)
        Else
          If SearchDirection = xlNext Then
            Set c = Where.FindNext(c)
          Else
            Set c = Where.FindPrevious(c)
          End If
        End If
        'Can happen if we have merged cells
        If c Is Nothing Then Exit Do
      Loop Until FirstAddress = c.Address
    
      'FastUnion algorithm © Andreas Killer, 2011:
      'Get all cells as fragments
      ReDim Temp(0 To Stack.Count - 1)
      i = 0
      For Each Item In Stack
        Set Temp(i) = Item
        i = i + 1
      Next
      'Combine each fragment with the next one
      j = 1
      Do
        For i = 0 To UBound(Temp) - j Step j * 2
          Set Temp(i) = Union(Temp(i), Temp(i + j))
        Next
        j = j * 2
      Loop Until j > UBound(Temp)
      'At this point we have all cells in the first fragment
      Set FindAll = Temp(0)
    End Function
    

    • Marked as answer by John.P.Smith Thursday, October 8, 2015 7:50 AM
    Wednesday, October 7, 2015 10:03 AM
  • Hi, Andreas :

    Thanks for your response. I tried this and it's really close. Please see below the result i get. 

    We want to remove only where all three are continuous ( semicolon, hash and number ). Associate 4 is a designation so we want to keep it. But before Associate 4 or for that matter after ;#number we want to remove.

    My data is like this before applyting this code:

    Level
    IT Analyst;#22
    Analyst;#7;#Associate 3 - Project;#37;#Associate 4 - Project;#9;#Engineer;#6
    Engineer;#6
    Associate 3 - Project;#37;#Associate 4 - Project;#9;#Analyst;#7;#Engineer;#6
    Analyst;#7;#Associate 3 - Project;#37;#Associate 4 - Project;#9;#Engineer;#6
    IT Analyst;#22
    IT Analyst;#22
    IT Analyst;#22
    IT Senior Engineer;#31
    IT Analyst;#22
    IT Systems Engineer 5;#26;#IT Systems Engineer 4;#25;#IT Analyst;#22;#IT Engineer;#28
    IT Analyst;#22;#IT Engineer;#28;#IT Systems Engineer 4;#25
    IT Analyst;#22;#IT Engineer;#28;#IT Systems Engineer 4;#25
    IT Analyst;#22;#IT Engineer;#28;#IT Systems Engineer 2;#23;#IT Systems Engineer 3;#24;#IT Systems Engineer 4;#25
    Engineer;#6;#IT Systems Engineer 3;#24;#IT Systems Engineer 4;#25;#IT Systems Engineer 5;#26;#IT Analyst;#22
    IT Systems Engineer 3;#24;#IT Analyst;#22
    IT Analyst;#22;#IT Engineer;#28
    IT Engineer;#28;#IT Analyst;#22
    Analyst;#7
    Analyst;#7
    Analyst;#7
    Analyst;#7;#Engineer;#6
    Analyst;#7

    After applying the code it become like below :

    Level
    IT Analyst,
    Analyst,;#Associate 3 - Project,;#Associate 4 - Project,;#Engineer,
    Engineer,
    Associate 3 - Project,;#Associate 4 - Project,;#Analyst,;#Engineer,
    Analyst,;#Associate 3 - Project,;#Associate 4 - Project,;#Engineer,
    IT Analyst,
    IT Analyst,
    IT Analyst,
    IT Senior Engineer,
    IT Analyst,
    IT Systems Engineer 5,;#IT Systems Engineer 4,;#IT Analyst,;#IT Engineer,
    IT Analyst,;#IT Engineer,;#IT Systems Engineer 4,
    IT Analyst,;#IT Engineer,;#IT Systems Engineer 4,
    IT Analyst,;#IT Engineer,;#IT Systems Engineer 2,;#IT Systems Engineer 3,;#IT Systems Engineer 4,
    Engineer,;#IT Systems Engineer 3,;#IT Systems Engineer 4,;#IT Systems Engineer 5,;#IT Analyst,
    IT Systems Engineer 3,;#IT Analyst,
    IT Analyst,;#IT Engineer,
    IT Engineer,;#IT Analyst,
    Analyst,
    Analyst,
    Analyst,
    Analyst,;#Engineer,
    Analyst,

    I'm really novice to excel macros. Please help me out with this. 

    Thanks again. 


    Wednesday, October 7, 2015 5:33 PM
  • Change the line

    If j > i + 2 Then

    to

    If j >= i + 2 Then
    • Marked as answer by John.P.Smith Thursday, October 8, 2015 7:51 AM
    Thursday, October 8, 2015 7:02 AM
  • Thank you.
    Thursday, October 8, 2015 7:51 AM