How To Remove Blank Spaces In Excel

In find what input bar insert a blank space using the space bar and replace with should remain blank.

How to remove blank spaces in excel. Select the cell or range of cells from which you want to remove white spaces. This removes the spaces anywhere from the selected cells. Select the cells containing spaces you will remove press the ctrl h keys to open the find and replace dialog box. The first way we can remove the extra unwanted spaces with the help of find and replace option ctrl h.

Remove leading and trailing spaces trim. There we need to put a space and replace that with blank nothing kept. The trim function below removes 2 leading spaces 3 extra spaces and 2 trailing spaces. Click insert module to create a new module script copy and paste below code to the script.

Trim clean text the clean function removes a range of non printing characters including line breaks and returns cleaned text. Remove leading spaces from strings with the vba code 5 steps 1. If you also need to remove line breaks from cells you can add the clean function like so. The trim function then takes over to remove extra spaces and returns the final text.

Use the shortcut key. Select the entire column you want to clean. There are multiple ways to remove spaces in excel. Think that if you work with a large data set in an excel workbook or excel spreadsheet there are times you might need to delete a blank cell or set of blank cells.

In the find and replace dialog and under the replace tab type one space into the find what textbox keep the replace with textbox empty and then click the replace all button. And now whenever you want to remove blank spaces in your excel sheets perform these 4 quick steps. Data doesn t always import or paste into excel as nicely as you want. The trim function in excel removes leading spaces extra spaces and trailing spaces.

Control h to activate find and replace option. In the replace with field type one space. Press f5 key to run the code then a. Using the search and replace short cut key.

Sometimes all the information is there but extra blanks and spaces are carried along with the data cells. Press alt f11 key to enable microsoft visual basic for applications window. Click the trim spaces button on the ribbon. Press the ctrl h shortcut to open excel s search and replace window.

Choose one or more options. In the find what field type two spaces.