none
Marco to remove numbers and certain special character from column RRS feed

  • Question

  • Hello,

    I have a Excel sheet that auto refreshes from Sharepoint, and the owner column "G" is transferred with additional characters, an example below:

    XXXX,X,Neil,XX XX;#139;#XXXX,X,Phillip C;#216

    I want to write a macro that when assigned to a button does 3 things:

    1. Refreshes data from Sharepoint

    2. Removes numbers from column "G"

    3. Removes the special character "#" from column "G"

    Can someone please help? I'm a novice to VBA so any help appreciated.

    Many thanks in advance

    Jon

    Thursday, March 15, 2018 10:14 AM

All replies

  • Hi Jon,

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

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    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


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, March 16, 2018 5:59 AM
  • Hi manner999,

    1. Refreshes data from SharePoint.

    Refer example below to update the data from SharePoint.

    Sub UpdateSPList()
    
       Dim ws As Worksheet
       Dim objListObj As ListObject
    
       Set ws = ActiveWorkbook.Worksheets(2)
       Set objListObj = ws.ListObjects("Table1")
    
       objListObj.UpdateChanges xlListConflictDialog
    
    End Sub

    Reference:

    Update Sharepoint list item from within Excel VBA

    2. Removes numbers from column "G"

    3. Removes the special character "#" from column "G"

    Refer example below.

    Data in Column G looks like below.

    Code:

    Sub demo()
    Dim i As Long
    Dim sht As Worksheet
    Dim LastRow As Long
    
    Set sht = ActiveSheet
    LastRow = sht.Cells(sht.Rows.Count, "G").End(xlUp).Row
    For i = 1 To LastRow
        
      Cells(i, 7).Value = RemoveNumbers(Cells(i, 7).Value)
      Cells(i, 7).Value = RemoveSpecialChrs(Cells(i, 7).Value)
     ' Debug.Print Cells(i, 7).Value
    Next i
    End Sub
    Function RemoveNumbers(Txt As String) As String
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[0-9]"
    RemoveNumbers = .Replace(Txt, "")
    End With
    End Function
    
    Function RemoveSpecialChrs(Txt As String) As String
    With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[#]"
    RemoveSpecialChrs = .Replace(Txt, "")
    End With
    End Function
    

    Output:

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 19, 2018 2:12 AM
    Moderator
  • Hi manner999,

    Is your issue is solved now?

    I find that you did not done any follow up after creating this thread.

    If your issue is fixed by you then I suggest you to post your solution and mark it as an answer.

    If your issue is still exist then try to refer the suggestions given by the community members.

    If you think that the suggestions given by the community member can solve your issue then mark the helpful suggestion as an answer.

    It will help us to close this thread and it also can be helpful to other community members who will meet with same kind of issues in future.

    If you have any further questions then you can let us know about it, We will try to provide you further suggestions to solve it.

    I suggest you to update the status of this thread and take appropriate actions to close it.

    Thanks for your understanding.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 21, 2018 9:49 AM
    Moderator