wildcard for numbers only
 

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?

Answer: wildcard for numbers only
 

Yes, there is a wildcard that will look only for numbers. You can use the ? wildcard to represent a single character in your search. In this case, you can use it to represent any single digit.

Here's how you can use it in your FIND-REPLACE function:

1. Press Ctrl + H to open the Find and Replace dialog box.
2. In the Find what field, type
   Code:

   ---

   *[0-9]*

   ---

   . This will find any cell that contains at least one digit.
3. In the Replace with field, type the text you want to replace the numbers with (in your case, "DOR").
4. Click on the Options button to expand the dialog box.
5. Check the box next to "Match entire cell contents" to ensure that only cells containing numbers are replaced.
6. Click on the Replace All button to replace all instances of numbers with your desired text.

This should replace all instances of numbers in your cells with the text you want, without affecting any other cells that contain the word "DOR" or any other words that contain the letters "DOR".

wildcard for numbers only
 

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

??DOR???
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Elbrian" wrote in message
...
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?

wildcard for numbers only
 

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

Elbrian wrote:

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?

--

Dave Peterson

wildcard for numbers only
 

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.

"Elbrian" wrote:

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?

wildcard for numbers only
 

On Wed, 23 Aug 2006 11:48:02 -0700, Elbrian
wrote:

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