Help with replacing a numerical range with one value

**Lechoz** · 01-24-2013, 02:44 AM

Dear all,

I have been trying to replace a range of numbers with one value, which initially did not seem like a complex request of excel. Either I am missing something really obvious, or it cannot be done with a simple Find/Replace request.

What I am exactly trying to do: I have a spreadsheet streching across 17 columns with a variety of numercial values in some cells. Most of the cells however, are populated with the characters "nd". What I would like to do is to replace all numbers that are greater than or equal to 40 with the value "A", and anything under 40 with the value "B".

Is this possible, and if so, how can I go about getting this little task done? Any advice would be muchly appreciated!

**FDibbins** · 01-24-2013, 02:58 AM

Hi Lechoz and welcome to the forum

You could indeed use find/replace for this, but you would have to do it individually for each number.

An alternative would be to use a formula in a "2nd" table next to your data, and use a formula like...
=IF(ISNUMBER(A1),IF(A1>=40,"A","B"),"nd")
copy this across and down, to cater for all rows/columns in your table

You can then either leave the original data there (hide it if needed), or copy/paste values over it with the answers from those formulas. you can then delete all the forumulas
(or you can wait for some-one to write some VBA code do do it for you )

**Lechoz** · 01-24-2013, 11:41 PM

Thanks for the welcome FDibbins. Also thank you for the help. It worked a treat! You have saved me a lot of mind numbing work, for which I am grateful. A bit slow cutting and pasting an 8K+ row data set, but much better than doing it all manually!

**FDibbins** · 01-25-2013, 12:46 AM

Happy to help. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)