Find and Replace Question
 

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.

Find and Replace Question
 

Sub fixum()
For Each r In Selection
If IsNumeric(r) Then
Else
r.Value = "Check"
End If
Next
End Sub

--
Gary''s Student - gsnu2007h


"Dow" 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.

Find and Replace Question
 

On May 7, 2:47 pm, Dow 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/

Find and Replace Question
 

On May 7, 1:14*pm, T Lavedas wrote:
On May 7, 2:47 pm, Dow 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?

Find and Replace Question
 

Hi Dow:

I should have mentioned:

You need to Select a block of cells before running the macro.
--
Gary''s Student - gsnu200784


"Dow" wrote:


On May 7, 1:14 pm, T Lavedas wrote:
On May 7, 2:47 pm, Dow 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?