Deleting all instances of # + 1-3 additional numbers accross a workbook

[mpouliot8168]

Hello,

This is my first post. I have a report that is exported from Sharepoint that gives me these item numbers that look like this

Item X #80; Item Y #126; Item Z #1

I would like to run a marco that would get rid of the # + the number that follows. The numbers can range from 1-1000. The result would look like this:

Item X; Item Y; Item Z

I would like to have this apply to the entire workbook not just the worksheet. Also this needs to run until all instances of the # + number removed. Then I would like to have a Msgbox appear that says that its has been completed. Thank you in advance for your replies!

 

[Mohammad Basem]

Did you try Excel's Find/Replace? It can do the job.

Enter text between the brackets
1st pass
Find what: [ #*;]
Replace with: [;]
This will clean items X & Y

2nd pass
Find what: [ #*]
Replace with: [] (empty)
This will clean item Z

This works just fine with Excel 2007

 

[JoeMo]

Try this on a copy of your workbook:

Sub RemoveNums()
Dim wb As Workbook, sh As Worksheet, c As Range

Set wb = ActiveWorkbook
For Each sh In wb.Worksheets
    On Error Resume Next
    For Each c In sh.UsedRange.SpecialCells(xlCellTypeConstants)
        If Err.Number <> 0 Then
            On Error GoTo 0
            Exit For
        End If
        If c.Value Like "* [#][0123456789]*" Then
            c.Replace " #*;", ";"
            c.Replace " #", ""
        End If
    Next c
Next sh
End Sub

 

[JoeMo]

Oops, dropped a widcard character in my previous post - amended version below:

Sub RemoveNums()
Dim wb As Workbook, sh As Worksheet, c As Range

Set wb = ActiveWorkbook
For Each sh In wb.Worksheets
    On Error Resume Next
    For Each c In sh.UsedRange.SpecialCells(xlCellTypeConstants)
        If Err.Number <> 0 Then
            On Error GoTo 0
            Exit For
        End If
        If c.Value Like "* [#][0123456789]*" Then
            c.Replace " #*;", ";"
            c.Replace " #*", ""
        End If
    Next c
Next sh
End Sub

 

[mpouliot8168]

Thank you for your responses. Unfortunately both approaches dont work.

So the simplier approach listed first works for the "2nd Pass" and gets rid of the #(number) but the "1st Pass" deletes all the information in the cell after the first instance.

The macro doesnt work there is a looping error. It comes in at "exit for".

Please let me know how I can fix this.

Thank you!

 

[Mohammad Basem]

Just tested it and works fine.

It gives the anticipated results, i.e. Item X; Item Y; Item Z

The difference between the 2 passes is the semicolon ";" which marks the 1st and 2nd parts.

If you omit the semicolon, the whole text will be deleted.

Make sure you 1st use [ #*;] and replace with [;] then use [ #*] and replace with [] (empty)

This works fine with Excel 2007

 

[mpouliot8168]

The item changes from this:

<TABLE style="WIDTH: 426pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=568><COLGROUP><COL style="WIDTH: 426pt; mso-width-source: userset; mso-width-alt: 20772" width=568><TBODY><TR style="HEIGHT: 127.5pt" height=170><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 426pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 127.5pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=170 width=568>Item Name;#317;#Item Name;#318;#Item Name;#319;#Item Name;#320;#Item Name;#321;#Item Name;#322;#Item Name;#323;#Item Name;#324;#Item Name;#325;#Item Name;#327


</TD></TR></TBODY></TABLE>


to this....


<TABLE style="WIDTH: 426pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=568><COLGROUP><COL style="WIDTH: 426pt; mso-width-source: userset; mso-width-alt: 20772" width=568><TBODY><TR style="HEIGHT: 127.5pt" height=170><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: white 0.5pt solid; WIDTH: 426pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 127.5pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=170 width=568>Item Name;;;;;;;;;;;;;;;;;;;#327</TD></TR></TBODY></TABLE>


Please advise.

Thank you.

 

[Mohammad Basem]

This is completely different than your 1st example. The semicolon is between the Item and #xxx

In your first sample you wanted to get Item X; Item Y; Item Z.

Please explain what is the anticipated result of the data shown?

 

[mpouliot8168]

I apologize for the change. The desired result is still the same:

Item Name;Item Name;Item Name;Item Name;Item Name;Item Name;Item Name;Item Name;Item Name;Item Name;Item Name;

I want just the item names with a ; separating each. Each cell can have aadifferent amount of items.

Thank you

 

[Mohammad Basem]

Try
1st pass
Find what: [;#*#]
replace with: [;]

2nd pass
Find what: [#*]
replace with: [] (empty)