Using # wildcard in VBA replace problem

[Fen_Tiger]

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.

 

[Andrew Poulsom]

Did you try replacing "??? - " (no quotes)?

 

[Fen_Tiger]

Hi Andrew,

I did try leaving out the quote marks, but I got a syntax error.

 

[Andrew Poulsom]

I mean no quotes when done manually. Here's what the macro recorder gave me:

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]

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.

 

[Andrew Poulsom]

The # sign isn't a wildcard character (they are ? and *).