Excel is a whizz at totalling numbers, but if too much copy-and-pasting and editing has turned some numbers in your spreadsheet into text format, then the results are often way out of whack. It’s almost impossible to fix that kind of problem without some manual intervention, but the TRIM and CLEAN functions can make the task simpler.
The Microsoft Office Blog details how a combination of the TRIM, CLEAN and ‘Convert to Number’ functions in Excel can take numbers that are surrounded by extraneous data and make them usable again. Getting badly-formatted spreadsheets isn’t uncommon, so it’s good to know about these options even if you’ll still need to double-check the results afterwards. Hit the link below for the full process, and share your own spreadsheet cleanup tips in the comments.
Repairing numbers formatted as text [Microsoft Office Blog]