VBA Code for Excel to Replace Every Other Semi Colon

[Adrian01]

I have a SharePoint list that I export into an Excel sheet. I run a macro that helps me quickly format this list into a printer friendly layout. I have one column though that includes the names of 1 to 6 people that I'm having a hard time formatting. If I have a single name in a cell, it appears as such before I run the macro: "Doe, John E;#28" and I have no problems getting rid of the extrenuous characters so that I only have the person's name left "Doe, John E". I use the following code to accomplish this:

Dim Owner As String
For Each lr In oList.ListRows
Owner = Intersect(lr.Range, oList.ListColumns("Owner").Range).Value
Owner = Replace(Owner, "#", "")
Owner = Replace(Owner, ";", "")
Owner = Replace(Owner, "1", "")
Owner = Replace(Owner, "2", "")
Owner = Replace(Owner, "3", "")
Owner = Replace(Owner, "4", "")
Owner = Replace(Owner, "5", "")
Owner = Replace(Owner, "6", "")
Owner = Replace(Owner, "7", "")
Owner = Replace(Owner, "8", "")
Owner = Replace(Owner, "9", "")
Owner = Replace(Owner, "0", "")
Intersect(lr.Range, oList.ListColumns("Owner").Range).Value = Owner
Next lr

The problem I run into is when I have more than one name in the cell and this information appears as such before I run the macro: "Doe, John E;#34;#Doe, Jane;#152;#Doe, Jim A;#158". After I run the macro, the information in this cell would then appear as such "Doe, John EDoe, JaneDoe, Jim A". You'll notice that there is now no space between the different names. What I would like to do is delete the odd numbered semi-colons (as is currently happening), but replace the even numbered semi-colons with a new line (vbNewLine). Any help with the code that will allow me to do this would be greatly appreciated!

-Adrian-

 

[Rick Rothstein]

Sorry about the confusion Rick. To answer your questions, the # sign appears after each name and yes I would like to get rid of them.

The numbers also appear after each name and I would like to get rid of them as well. So, I would like the end product to look like this:

Doe, John E
Doe, Jane
Doe, Jim A

Thanks again for your willingness to help with this!

In that case, try PGC's code (Message #4) with the change I posted (Message #5), namely this...

' Code posted by pgc01
With Range("A1")
    .Value = .Value & "#"
    .Replace What:="#*#", Replacement:=vbNewLine, LookAt:=xlPart
End With

 

[DeBeuz]

Hi Adrian,

Thas's weird. I have tested this code on Excel 2007 and for me it worked.
I only have tested the inside not your external for loop and setup of owner.

Paul

 

[DeBeuz]

Hi Adrian,

Tested it again:

Sub test()
    Const RemChars = "#0123456789"
    
    Dim I As Integer
    Dim Owner As String
    Dim DoReplace As Boolean
    Dim Wrk As String
    
    'For Each lr In oList.ListRows
        'Owner = Intersect(lr.Range, oList.ListColumns("Owner").Range).Value
        'Replace even ; bu vbCrLf
        Owner = "Doe, John E;#34;#Doe, Jane;#152;#Doe, Jim A;#158"
        Debug.Print "Owner >" & Owner & "<"
        DoReplace = False
        For I = 1 To Len(Owner)
            If Mid(Owner, I, 1) = ";" Then
                If DoReplace Then Wrk = Wrk & vbCrLf
                DoReplace = Not DoReplace
            Else
                'Copy the char
                Wrk = Wrk & Mid(Owner, I, 1)
            End If
        Next I
        Owner = Wrk
        'Now replace unwanted chars
        For I = 1 To Len(RemChars)
            Owner = Replace(Owner, Mid(RemChars, I, 1), "")
        Next I
        Debug.Print "Owner >" & Owner & "<"
    'Next lr
End Sub

Result:
Owner >Doe, John E;#34;#Doe, Jane;#152;#Doe, Jim A;#158<
Owner >Doe, John E
Doe, Jane
Doe, Jim A<

 

[Adrian01]

Rick,
I'm starting to get excited! I used your code for a specific cell and it worked perfectly! Now I'm trying to have it run on entire entire column in a table (titled "Owner"), but am running into some problems doing that. The way I have the code written currently is as follows:

Dim Owner As String
For Each lr In oList.ListRows
Owner = Intersect(lr.Range, oList.ListColumns("Owner").Range).Value
Value = .Value & "#"
Replace What:="#*#", Replacement:=vbNewLine, LookAt:=xlPart
Intersect(lr.Range, oList.ListColumns("Owner").Range).Value = Owner
Next lr

When I try to run the code, I get an error message that reads "Compile error: Invalid or unqualified reference" and it highlights the .Value portion of the code. What do you think? Any tips on how to write this so that the range is the entire column instead of a specific cell?

 

[Adrian01]

Paul,
My guess is that this is purely operator error (as in my error). I'm still pretty new to VBA (obviously) and I'm likely not entering your code in properly. I apologize for any confusion/frustration. I really appreciate the time/effort you've spent trying to help me though.

-Adrian-

 

[Rick Rothstein]

Rick,
I'm starting to get excited! I used your code for a specific cell and it worked perfectly! Now I'm trying to have it run on entire entire column in a table (titled "Owner"), but am running into some problems doing that. The way I have the code written currently is as follows:

Dim Owner As String
For Each lr In oList.ListRows
Owner = Intersect(lr.Range, oList.ListColumns("Owner").Range).Value
Value = .Value & "#"
Replace What:="#*#", Replacement:=vbNewLine, LookAt:=xlPart
Intersect(lr.Range, oList.ListColumns("Owner").Range).Value = Owner
Next lr

When I try to run the code, I get an error message that reads "Compile error: Invalid or unqualified reference" and it highlights the .Value portion of the code. What do you think? Any tips on how to write this so that the range is the entire column instead of a specific cell?

Try it this way instead...

Sub RemovePoundSigns()
  Dim Addr As String
  Addr = Range("B2", Cells(Rows.Count, "B").End(xlUp)).Address
  Range(Addr) = Evaluate("IF(" & Addr & "="""",""""," & Addr & "&"";#"")")
  Range(Addr).Replace What:=";#*#", Replacement:=vbNewLine, LookAt:=xlPart
End Sub

 

[Adrian01]

Rick,
This works perfectly! Thank you so much!!! If only you knew how much angst you have saved me.

-Adrian-

 

[Rick Rothstein]

Rick,
This works perfectly! Thank you so much!!! If only you knew how much angst you have saved me.

You are quite welcome... I am glad I was able to be of some help to you. However, you should also offer some thanks to pgc01 as well since I used what he posted in Message #4 at the heart of the code I posted for you.