Re: Find and Replace Question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


On May 7, 1:14 pm, T Lavedas <tglba...@xxxxxxx> wrote:
On May 7, 2:47 pm, Dow <dfairbank...@xxxxxxxxxxx> wrote:





If I have a column that has both numbers and words is there a way to
replace the words with an alternate value? For example the column may
read:

123456
1234567
adam
4569
eve
111-111

If I were to sort the above column the "adam", "eve", and "111-111"
would sort as words and not numbers obviously.

I want to write a macro to look at the column and take all of the word
entries, ignore the numbers and replace with another word, in this
case "Check".  When finished the above would read:

123456
1234567
Check
4569
Check
Check

I found some references here about isnumeric and istext and variations
of this but I was not able to make it work for what I was attempting.

Thanks for any help.

This worked for me ...

=IF(ISNUMBER(A2),A2,IF(ISTEXT(A2),"Check",NA()))

The formula is in the second row and in a column to the right of
column A, say B.  The data to test is in column A (same row).  Copying
the formula down the column (B, in my test) gave me the results you
indicated.

Is that what you're after?  If not be more illustrative.

Tom Lavedas
===========http://members.cox.net/tglbatch/wsh/- Hide quoted text -

- Show quoted text -

Thank you for the responses. Let me try some more information.

This is Column N. "Status" is the header. The number of rows and the
data will vary widely:

Status
123456
1234567
adam
4569
eve
111-111

When I use the first code it changes only the active cell and ignores
the rest of the column. It does perform correctly for that one cell
though.

The second formula works as a formula, but I need a macro. There are
upwards of 80,000 rows and to copy the formula down all of them, then
copy, paste, and sort in VB seems cumbersome.

I can do this for any number of different values:

Cells.Replace What:="adam", Replacement:="Check", LookAt:=xlWhole,
MatchCase:=False

Unforunately due to the changing nature of the values the list of
"Replace What" would be very long and always in need of update.

The only values I have to worry about are the non-numeric. I want
something as simple as

Range("N1").Select
Cells.Replace What:=IFTEXT, Replacement:="Check"

Does anybody know of anything like that or a way to word what I have
to make it work?
.

Relevant Pages

  • Re: Find and Replace Question
    ... would sort as words and not numbers obviously. ... The formula is in the second row and in a column to the right of ...
    (microsoft.public.excel.programming)
  • Re: 7-Foot Robot Used in Black Sea Expedition
    ... Revising my interpretation of the story of Adam and Eve: ... In the Hellenistic period of time the Taurus ... The river of Eden -- might have been the clouds along the Taurus ...
    (sci.archaeology)
  • Re: 7-Foot Robot Used in Black Sea Expedition
    ... Revising my interpretation of the story of Adam and Eve: ... In the Hellenistic period of time the Taurus ... The river of Eden -- might have been the clouds along the Taurus ...
    (sci.lang)
  • Re: a couple questions for our creationist friends
    ... original sin or the teaching that Adam and Eve are our first parents? ... These doctrines are the teaching of the ordinary magisterium. ... placed a limitation on the exploration of evolution, ...
    (talk.origins)
  • Re: a couple questions for our creationist friends
    ... original sin or the teaching that Adam and Eve are our first parents? ... These doctrines are the teaching of the ordinary magisterium. ... placed a limitation on the exploration of evolution, ...
    (talk.origins)