How to Remove Whitespace From a String in Excel

Save

Microsoft Excel comes with an arsenal of tools that can help you efficiently perform data clean-up tasks. For instance, to remove whitespace from a string in Excel, there are three quick-and-dirty methods that you can choose from. With Microsoft's SUBSTITUTE function, you can make sure that formulas, names, and cell references do not include whitespace in their output. If your data range contains string values only and no formulas, you can clear out spaces with a simple "Find and Replace" query or by running a short macro.

Using the SUBSTITUTE function

  • Launch Excel. Create a new workbook, or open the workbook that you'd like to edit.

  • Enter the formula below into an empty cell in your worksheet.

    =SUBSTITUTE(A1," ","")

  • Replace A1 with a reference to the cell containing the string from which you'd like to remove spaces. You may also replace A1 with an actual string (must be enclosed in quotation marks) or a formula that outputs a string value. Press "Enter" to see the result.

Using "Find and Replace"

  • Launch Excel. Create a new workbook, or open the workbook that you'd like to edit.

  • Highlight the cells that contain string values from which you'd like to remove spaces.

  • Open the "Find and Replace" window by holding down "Ctrl" and pressing "H."

  • Click the text field beside "Find what" and press your space bar once. Leave the "Replace With" field empty.

  • Click the "Replace All" button. If you selected only one cell, click the "Replace" button instead.

Using a Macro

  • Launch Excel. Create a new workbook, or open the workbook that you'd like to edit.

  • Open the macros window. To do this, hold down "Alt" and press "F8." Enter "RemoveWhiteSpace" under "macro name" and then click on "Create."

  • Copy and paste the following code into the VBA editor.

    Sub RemoveWhiteSpace()

    Dim Rng As Range

    For Each Rng In Selection.Cells

    Rng = Replace(Rng, " ", "")

    Next

    End Sub

  • Close the VBA editor and highlight the cells from which you'd like to remove spaces.

  • Hold down "Alt" and press "F8" to open the macros window again. Double-click on "RemoveWhiteSpaces" to run the macro.

Tips & Warnings

  • To remove leading or trailing spaces only, use Excel's built-in TRIM function. Its syntax is "TRIM(string)," where "string" could be a reference to a cell containing a string value.
  • If you are running Windows 2007 or higher, you may not have access to the macros window and the developer tab by default. To enable macros, click the Microsoft Office button and then click on "Excel Options." Choose "Popular" from the Category list and make sure that "Show Developer tab in the ribbon" is checked.

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

Geek Vs Geek: Robot battles, hoverboard drag race, and more

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!