If you have Excel 2013 or later, you can also try using Flash Fill to replace spaces. To convert the formula to text, simply copy and paste the formula as values. If you need to remove leading and trailing spaces, you can combine the SUBTITUTE function with the TRIM function as follows: Text must appear in quotation marks or double quotes.įor example, if you want to replace spaces with underscores in cell B1 based on a text string in A1, the formula would be: =SUBSTITUTE(cell containing the data you want to replace, the value you want to remove, the value you want to use as a replacement, instance if required) The simple syntax of the SUBSTITUTE function is: The new text is the text you use as a replacement. The old text is the text you want to replace. The text is typically a reference to a cell. The Excel syntax for the SUBSTITUTE function is: You can also create a formula and use the SUBSTITUTE function to replace spaces in text strings. A dialog box appears indicating the number of replacements.īelow is the Find and Replace dialog box in Excel to replace spaces with underscores: If you want to replace the space with nothing, leave the box blank. In the Replace with box, type an underscore, dash, or other value.You can also click the Home tab in the Ribbon and select Replace in the Find & Select group. Press Ctrl + H to display the Find and Replace dialog box.
![mac excel find and replace mac excel find and replace](https://www.techonthenet.com/excel/questions/images/column_letters2011_001.png)
Select the range of cells containing text strings that include spaces you want to replace.To replace spaces using Find and Replace:
MAC EXCEL FIND AND REPLACE HOW TO
Recommended article: How to Delete Blank Rows in Excel (5 Fast Ways to Remove Empty Rows)ĭo you want to learn more about Excel? Check out our virtual classroom or in-person Excel courses > 1. In Excel, you can replace spaces in text strings with underscores, dashes or other values using Find and Replace, by creating a formula or by using Flash Fill. 3 Ways to Replace Spaces Between Characters in Excel Text Stringsīy Avantix Learning Team | Updated October 20, 2021Īpplies to: Microsoft ® Excel ® 2013, 2016, 2019, 2021 and 365 (Windows)