This past week I faced a tedious chore. I received a document that needed a lot of formatting work before I could pass it along. To me, it looked like a long session of repetitive keyboarding, but I knew there had to be a better way, and of course, there was. Word has a single keystroke method for repeating a task—what is it?
Last week we asked…
Why won’t Excel add the numbers from Access that I just inserted into my sheet? Yes, it was an easy challenge, but not all challenges are difficult. Sometimes the challenge is in deciding which solution is the best. In this case, there’s more than one way to convert text—and no one way is really better than the other. Sometimes, the situation at hand will determine which route you take.
Tink suggested changing the format to Number and that’s definitely one solution. Formatting doesn’t take too many clicks, but there is another route. Select the range of values in question, choose Copy from the Edit menu, or press [Ctrl]+C. Then, choose Paste Special from the Edit menu, click the Values option in the Paste section and None in the Operation section (the default), and click OK.
This problem is less apparent in older versions of Excel—recent versions use a smart tag to display an error, which tells you exactly what the problem is! When this is the case, the easiest solution is to simply display the error list and choose Convert To Number! Excel will make the conversion and remove the error tag.
Len222’s suggestion of using the Paste Special feature’s Multiply operation is especially helpful if you’re trying to convert noncontiguous ranges, as the above error conversion only works on contiguous ranges (at least, I can’t make it work on non-contiguous ranges).
If you don’t have the luxury of permanently converting the values in the sheet, take a look at Excel’s VALUE() function. This function converts a text value that represents a number to a number, within the context of that function. In other words, if cell A1 contains the text value ‘1, the expression =VALUE(A1) will return a numeric 1, which Excel can then evaluate mathematically.