This process will allow you to change a data set into numbers when the format is something other than what you need  (when numbers in a column won’t sum together because they are formatted as text or something else besides a number).

The Excel functionality is called text-to-columns.  It allows data to “fit appropriately” in that cell (without weird spaces or tabs that you may not even notice, and prohibiting proper calculations). This procedue also formats the entire column to be a data type of your choosing, solving our calculation issues.

Text-to-Columns can be used to separate segments of a data cell into multiple cells but that is not covered here.

STEPS:

Once your file is open and you have identified the numbers you need formatted, select the entire range, or column.

text-to-columns1

Next step,  press Alt, then D, then E.  

Alternatively, you can go through the top ribbon menu by selecting the DATA Tab, then Text to Columns choice.

text-to-columns2

This will bring up the “Convert Text to Columns Wizard – Step 1 of 3”  dialog window.

text-to-columns3

From this window you can either press W, or manually select the Fixed Width radio button.  

Then hit Next to go to Step 2.

text-to-columns4

Hit Next again to go to Step 3 of 3

text-to-columns5

If you solely want to convert the data to numbers, hit Finish.

Alternatively, you can change cells to a different formats by selecting any of the radio button choices shown.  You can also move the data to another destination.

Now that you have performed this action, you will notice your numbers snap to the right.  This is a good sign, as they are now formatted appropriately to ‘General’ and will sum or perform mathematical operations.   If you investigate further by pressing Ctrl-1, you will see the format chosen as ‘General’.text-to-columns6

The quick keystroke method:  First select the column, then press

   ALT,  D, E, W,  return, return, return.