| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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/ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple Find/Replace question | Excel Programming | |||
| Find and Replace Question | Excel Programming | |||
| Find & replace question.....I believe | Excel Discussion (Misc queries) | |||
| Find & Replace question | Excel Programming | |||
| Find/Replace Question | Excel Discussion (Misc queries) | |||
Hybrid Mode
