Forum Discussion
Find and Replace Range
Is it possible to use the find and replace feature to find a specific range of numbers between 0-100 in multiple rows/columns cells and replace it with a range value? For example if i had various numbers ranging from 0 to 100 in multiple cells, could i use find to find all numbers between 0 and 10 and replace the cell value with the range of 0-10? If not i would be open to simple workarounds to accomplish the same result.
sirk78 you can use the IFS() statement:
=IFS(A1<=10,"1-10",A1<=20,"11-20",A1<=30,"21-30",A1<=40,"31-40", ... , A1<100,"91-100")or try this:
=MROUND(A1+4.99,10)-9 & " - " & MROUND(A1+4.99,10)which will work even past 100 but does assume 0.01 threshold (i.e. 10.001 will still fall under 1-10 instead of 11-20), but you can tweak that as you wish.
4 Replies
sirk78 How about creating another column with a formula that does it for you? For example you data is in column A and you put this in col B (starting at row 1):
=IFERROR(IF(AND(A1>=1,A1<=10),"1 - 10",A1),"")if you have multiple columns like A:F then put it in G and copy down and right to L.
If you don't want the extra columns you can then cut and paste VALUES back over the orig data or maybe better yet is just HIDE those original columns (highlight A:F and right click and select hide).
- I had thought about nesting if statements but the values I am working with are 1-100 being grouped in ranges of 10 which would mean 10 if statements and my understanding is that the max is 7. Would this produce the same problem? Thanks
sirk78 you can use the IFS() statement:
=IFS(A1<=10,"1-10",A1<=20,"11-20",A1<=30,"21-30",A1<=40,"31-40", ... , A1<100,"91-100")or try this:
=MROUND(A1+4.99,10)-9 & " - " & MROUND(A1+4.99,10)which will work even past 100 but does assume 0.01 threshold (i.e. 10.001 will still fall under 1-10 instead of 11-20), but you can tweak that as you wish.
