Using # wildcard in VBA replace problem

Fen_Tiger

New Member
Joined
Jul 23, 2010
Messages
7
Hi. Hope someone can help me.

I'm using a macro to reformat a risk register in Excel to present to the board, so I'm looking to strip out some superflous details. The text in cells cells in column N looks like this:

006 - Restructure of senior management team and organisation (Chief Executive, 30/11/2009, High)
079 - Stronger Coordination and alignment of strategic initatives across business
101 - Involvement of Trustees in key projects (Chief Executive, , Not Yet Entered)
115 - Good external communication plan (Chief Executive, , Not Yet Entered)

I'm having trouble removing the initial numbers. I'm currently using this code:

Columns("N:N").Replace What:="0???", Replacement:="", LookAt:=xlPart, _SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False

This quite happily removes zero and the subsequent 3 characters from anywhere in the cell. The problem comes from numbers like 101 where "01 -" is removed (but not the leading 1). I understand # should act as a wildcard for numbers, but if I replace "0???" with "###?", nothing happens, no characters are removed.

I can't seem to figure out what I'm doing wrong or find any similar queries/ posts to help. The cell formatting doesn't seem to have an effect.

Any help most gratefully received.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I mean no quotes when done manually. Here's what the macro recorder gave me:

Code:
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 08/03/2011 by Andrew
'
'
    Selection.Replace What:="??? - ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
 

Fen_Tiger

New Member
Joined
Jul 23, 2010
Messages
7
D'oh! You know when you read something too quickly for your own good...

Thanks Andrew, that worked a treat. Don't know why I didn't/ couldn't see it. I'm still curious if anyone knows why the # wildcard wouldn't work.
 

Forum statistics

Threads
1,141,849
Messages
5,708,973
Members
421,602
Latest member
jkpce1880

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top