Method 5 – Insert the SUBSTITUTE Function to Erase All Spaces in Excel The required formula with theSUBSTITUTEfunction to replace a regular space with no space will be: =SUBSTITUTE(B5," ","") After pressingEnter, find your texts with no space. Method 6 – Use the MID Function to Delete...
Read More: How to Remove Space Before Text in Excel Method 2 – Remove Spaces after Text Using the TRIM Function in Excel Case 2.1 – Using the TRIM Function Only Use the following formula in cell C5. =TRIM(B5) Press Enter. Drag the Fill Handle down to AutoFill the column. Case 2.2 ...
Observe the results after applying a trimming function. The length of cells after the trim is 7. Still, there is a space between–and5in the first row, and there is a space between6and/in the second row. Now apply theSubstitutefunction and check the lengths. The substitute function is u...
You can use all these methods on Excel and if you are not comfortable with using Excel, you can try a free alternative calledWPS Office. With WPS Office, you'll enjoy a plethora of features that are not available in Microsoft Office, it will be easier to find andremove space in excelbe...
3. Configure the Find Options:In the "Find what" field of the dialog box, press the spacebar once. This action inserts a space character, effectively indicating that you're seeking spaces in your data. Leave the "Replace with" field empty. Your Excel is now primed to detect all space ...
How to remove blank spaces in Excel - leading, trailing, between words If your data set contains superfluous spaces, theExcel TRIM functioncan help you delete them all in one go - leading, trailing and multiple in-between spaces, except for a single space character between words. ...
The second argument in this formula is a space, and the third argument is nothing. Thus, this formula will remove all the spaces in a cell. For the above two formulas, you can choose one formula according to your need. Method 3: Excel Macros ...
In theFind and Replacedialog, set as follows: Typea spaceinto theFind whattextbox. Leave nothingin theReplace withtextbox. ClickReplace All. ClickOKto finish. Result Effortlessly remove all spaces in Excel withKutools for Excel's powerfulRemove Spacesfeature. Save time and achieve precise results...
excel has a lot of functions and formulas to help you perform certain operations. to remove unwanted spaces between texts you can use the trim function. this function removes the excess leading, trailing, and spaces in-between values. however, there will exist a space that separates the two ...
Once done, pick the first cell in the Helper column, pressShift+F10, and paste the same by pressingCTRL+V. Remove the Helper column, and you'd have removed all extra spaces. RELATED:How to Use the TRIM Function in Excel Using the Trim Spaces Tool ...