How to remove spaces in cell data in Excel

This came up in the last Excel class. A student asked how to remove spaces in Excel cell data. For example a product number that was entered incorrectly.

In the below simple example we are going to use the SUBSTITUTE() fucntion in Excel to substitute a space ” ” with essentially nothing “”.

Excel Substitute Function

The above SUBSTITUTE fuction will replace  all of the spaces with nothing (removing the space).  NOTE: it is possible to add another variable to the function (instance_num) that will only change that instance of the value you are looking to substitute.

In this case the formula would look like

=SUBSTITUTE(A2," ","",1)

The above would only change the first instance of a space in the cell text it finds.

Hope this helps someone else.

Please leave questions or comments in the comment section.