wildcard for numbers only

[Guest]

I'm trying to do a FIND-REPLACE on a list of alpha-numeric figures. I want
to eliminate just the numbers. For instance, I want to REPLACE "45DOR143"
with "DOR". Trouble is when I do a FIND for "*DOR*" I also FIND "DORMANT"
and other cells which I don't want to replace and have to manually change
those words so they don't trigger the search. (I make the search case
sensitive and make a letter in the cell I don't want lower case).

Anyway, is there a wildcard which will look only for numbers?

 

[RagDyer]

If your data is *exactly* in the same configuration as you posted, you could
try this:

??DOR???

 

[Dave Peterson]

Not that I know of.

But you could use a little macro.

Select the range to fix and then run this:

Option Explicit
Sub testme01()

Dim iCtr As Long

For iCtr = 0 To 9
Selection.Replace What:=iCtr, Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

I would do this via Word. Copy to Word. In Word, go to Edit, Replace. Enable
Wildcards. In the Find What: field enter [0-9] and do not enter anything in
the Replace With: field. Press Replace All. Copy the contents of the cells to
Excel.

 

[Ron Rosenfeld]

I'm trying to do a FIND-REPLACE on a list of alpha-numeric figures. I want
to eliminate just the numbers. For instance, I want to REPLACE "45DOR143"
with "DOR". Trouble is when I do a FIND for "*DOR*" I also FIND "DORMANT"
and other cells which I don't want to replace and have to manually change
those words so they don't trigger the search. (I make the search case
sensitive and make a letter in the cell I don't want lower case).

Anyway, is there a wildcard which will look only for numbers?

No there is not.

One way to do this using formulas is to download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr

Then use the formula:

=REGEX.SUBSTITUTE(A1,"\d")


--ron