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-

 

[DeBeuz]

Hi,

Does this help you?

    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
        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
    Next lr

Paul

 

[Rick Rothstein]

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"

Do you actually have the # sign in front of the second, third, etc. names like you showed above? If so, I am guessing you want to get rid of them?

Also, did you want to retain the "#number" attached to the name or is that being eliminated completely? In other words, did you want the output to look like this in the cell...

Doe, John
Doe, Jane
Doe, Jim

or like this...

Doe, John E;#34
Doe, Jane;#152
Doe, Jim A;#158

where I have assumed you wanted to eliminate the # sign from in front of the name (see my first question above).

 

[pgc01]

Hi

If the strings in the cells always have a format like the one you posted:

Doe, John E;#34;#Doe, Jane;#152;#Doe, Jim A;#158

you could also delete all pairs of #...#

For ex., write the string in A1 and execute

With Range("A1")
    .Value = .Value & "#"
    .Replace What:="#*#", Replacement:="", LookAt:=xlPart
End With

 

[Rick Rothstein]

Hi

If the strings in the cells always have a format like the one you posted:

Doe, John E;#34;#Doe, Jane;#152;#Doe, Jim A;#158

you could also delete all pairs of #...#

For ex., write the string in A1 and execute

With Range("A1")
    .Value = .Value & "#"
    .Replace What:="#*#", Replacement:="", LookAt:=xlPart
End With

If that is the actual format (see my questions in Message #3), then the replacement should be vbNewLine (or vbLfCr) instead of "" because the OP wrote this in his original message... "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)."

 

[pgc01]

If that is the actual format (see my questions in Message #3), then the replacement should be vbNewLine (or vbLfCr) instead of "" because the OP wrote this in his original message... "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)."

Thank you Rick. You are right, of course. Instead of just deleting those #*# pairs it should replace them with the newline (or carriage return, etc.)

That if the format is right...

 

[Adrian01]

Paul,
Nothing happens when I try to use this code. It leaves all unwanted integers (;,#, 1,2,3,4,5,6,7,8,9, and 0). This includes both odd and even numbered semi-colons.

Thanks for trying

 

[Adrian01]

Rick and PGC,
I don't want to keep the # characters and currently have no problems removing them with the code entered in the initial post. The problem I run into is with the semi-colons (. I can remove them all with no problem, or have them all replaced with a carriage return (vbLfCr) with no problem. The problem I run into is when I want to delete the first semi-colon and have the second semi-colon replaced with a carriage return (vbLfCr). It's this requirement to do something different with the semi-colon depending on if it's the first or second semi-colon in-line that's giving me a problem.

Thanks for responding so quickly gentlemen.

-Adrian-

 

[Rick Rothstein]

Rick and PGC,
I don't want to keep the # characters and currently have no problems removing them with the code entered in the initial post.

We understand you can remove them, but doing so first may be the wrong way to approach your problem (the # signs could be useful in selecting what you want removed)... unfortunately, we cannot tell yet because you did not clarify what your data actually looks like for us. It would be real helpful if you would answer (all) the questions I asked in Message #3.

 

[Adrian01]

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!