Remove spaces in Excel cells - leading spaces, trailing spaces, extra spaces, or all spaces
Sometimes when data is copied and pasted into a spreadsheet from another application, an error may occur if extra spaces are accidentally added to the data. For example, if you add two numeric cells that contain spaces at the beginning, the result is 0 instead of the correct result; or when comparing two cells with the same content, but one of them has spaces at the beginning or end, you will get wrong results. How do I remove these spaces? This guide will introduce you to several methods.
Removes extra spaces from cells, including leading, trailing, and extra spaces
Remove only leading spaces from cells
Remove only trailing spaces from cells
remove all spaces from cells
Easily remove leading, trailing, extra and all spaces in cells with amazing features
Removes extra spaces from cells, including leading, trailing, and extra spaces
Suppose you have a list of columns of cells with leading, trailing and extra spaces. To remove all these extra spaces from a cell, the TRIM function can help.
= trim (text)
Select a blank cell, enter= skróć (B3)to it and pressNursekey. then drag itFilling handleApply the formula to other cells. Check the screenshot:
notes: B3 is the cell containing the text you want to remove spaces from.
Now all extra spaces including leading, trailing and extra spaces will be removed from specified cells.
notes: This operation must be performed in a new column. After removing all extra spaces, replace the original data with the shortened data by pressingControl key+C, select the original data range, right click and selectvaluespod belowgluing optionsOf.
Multiple clicks to remove unwanted spaces from cells
together withremove spacesa tool that helps to remove not onlyleading spaces, trailing spaces, extra spacesbut alsoall spacesFrom a single range, multiple ranges, or even a whole sheet, it will save a lot of work time.
Excel® Kutools- Integration of more than 300 advanced advanced functions, specially designed for more than 1500 operating scenarios, solving80%Problem with Excel.
Download and try a fully functional 30-day free trial
Remove only leading spaces from cells
In some cases, you may need to remove only the leading spaces and keep all spaces in the cells as shown in the image below. This section introduces another formula and VBA code to solve this problem.
Use the MID function to remove only leading spaces
Select a blank cell (here I select cell D3), copy the formula below into it and pressNursekey. then dragFilling handleApply the formula to other cells.
=CENTER(B3,NAI(CENTER(PATTERN(B3),1,1),B3),DEBT(B3))
In this formulaB3is a cell containing text with leading spaces removed.
notes: This operation must be performed in a new column. After removing all leading spaces, replace the original data with the truncated data without the formula.
Remove only leading spaces with VBA code
1. Open the sheet containing the cells you want to remove leading spaces from, and pressAlt+F11open keyMicrosoft Visual Basic for Applicationswindow.
2. PressPut in>module, then copy the VBA code below into the module window.
Sub RemoveLeadingSpaces()'Updateby20190612Dim Rng As RangeDim WorkRng As RangeOn 错误恢复 NextxTitleId = "KutoolsforExcel"Postavi WorkRng = Application.SelectionSet WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)Za svaki Rng In WorkRng Rng.Value = VBA.LTrim(Rng.Value)NextEnd Sub3. PressF5The key to run the code. ANDExcel® KutoolsA dialog box will appear, select the contiguous cells you want to remove leading spaces from and clickAll rightbutton.
Now you can see that only the leading spaces have been removed from the selected cells.
Remove only trailing spaces from cells
1. Open the sheet containing the cells you want to remove leading spaces from, and pressAlt+F11open keyMicrosoft Visual Basic for Applicationswindow.
2. PressPut in>module, then copy the VBA code below into the module window.
Sub RemoveTrailingSpaces()'Updateby20190612Dim Rng As RangeDim WorkRng As RangeOn 错误恢复 NextxTitleId = "KutoolsforExcel"Postavi WorkRng = Application.SelectionSet WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)Za svaki Rng In WorkRng Rng.Value = VBA.RTrim(Rng.Value)NextEnd Sub3. PressF5The key to run the code. ANDExcel® KutoolsA dialog box will appear, select the contiguous cells you want to remove trailing spaces from and clickAll rightbutton. Now you can see that only trailing spaces are removed from selected cells.
remove all spaces from cells
If you want to get rid of all spaces in a specific cell, the methods described in this section can help.
Use the SUBSTITUTE function to remove all spaces in a cell
=replace(text,old text,new text,[instance number])
Select a blank cell, copy the formula below into it, and pressNursekey. then dragFilling handleApply the formula to other cells.
=REPLACE(B3,"","")
edge: In this formulaB3is the cell containing the text to remove all spaces;
„”(space in double quotes) here represents the space to be removed;
„”Here it means you will replace all spaces with nothing.
Then you can see all spaces in the specified cell are removed immediately.
notes: This operation must be performed in a new column. After removing all spaces, the original data should be replaced with shorthand data without formula.
Remove all spaces from cells with Find and Replace
In addition to the abovereplacethe search and replace feature can also help you remove all spaces in cells.
1. Select a cell containing a space you want to delete, pressControl key+Hopen keyFind and replaceDialogue window.
2. tyFind and replacedialogue and morereplacetab, type a spacewhat to look fortext box, saveuse. . . replacethe text box is empty, then clickreplace everythingbutton.
Easily remove leading, trailing, extra and all spaces in cells with Kutools
of work: If you are tired of using formulas and VBA code to remove spaces thenremove spacesutilityExcel® Kutoolsit's your best choice. With just a few clicks, you can not only remove leading spaces, trailing spaces, extra spaces, but also remove all spaces in a range, multiple ranges, or even the entire worksheet, saving you a lot of time.
You need to take a few minutes before using Kutools for ExcelDownload and install for freeFirst.
1. Select one or more ranges containing the cells you want to remove spaces from, clickcool picture>text>remove spacesSee screenshot:
2. You see there are 5 options insideremove spacesdialog:
- To remove only leading spaces, selectleading spacepossibilities;
- To remove trailing spaces only, selectspaces at the endpossibilities;
- To remove leading and trailing spaces, selectleading and trailing spacespossibilities;
- To remove all extra spaces (including leading, trailing, extra spaces), selectall the extra spacepossibilities;
- To remove all spaces, selectall spacesoptions.
then clickAll rightstart button.
If you want to use the free trial version (30-dan) This tool,Click to download, then follow the steps above to apply the operation.
Other practical cases related to Excel space
In addition to removing spaces in cells, have you ever encountered counting, adding spaces, or replacing spaces with other characters in cells? The following suggestions can speed up your work in Excel.
Count the total number of spaces in a cell
Before you remove all spaces from a cell, you should know how many spaces there are in it. This tutorial provides a method with detailed steps to help you quickly get the total number of places in a cell.
Click to find out more...
Add spaces after every comma in some Excel cells
Sometimes spaces can be accidentally removed from some cells. This tutorial talks about adding a space after every comma to make the text string clearer and standardized, and the steps are also very detailed.
Click to find out more...
Add spaces between numbers in Excel cells
This tutorial is about adding a space between every number or every nth number in an Excel cell. Let's say you have a column of phone numbers and you want to add spaces between them to make the numbers look more intuitive and easier to read. The methods described in this guide will help you.
Click to find out more...
Add spaces before capital letters in Excel cells
This tutorial is about adding a space before every capital letter in Excel cells. Suppose you have a list of text strings with spaces accidentally removed, like this: InsertBlankRowsBetweenData, to add a space before each capital letter to separate words as Insert blank lines between data, try the method from this tutorial.
Click to find out more...
Replace spaces with specific characters in Excel cells
In many cases, you prefer to replace spaces with specific characters instead of removing them directly from cells. Here are some ways to easily replace cell spaces with underscores, dashes, or commas.
Click to find out more...
The best office productivity tools
Kutools for Excel solves most problems and increases productivity by 80%
- super formula bar(Simple editing of multi-line text and formulas);read the schedule(easy reading and editing of a large number of cells);Paste in the range of filters...
- Merge cells/rows/columnsand save the data, split cell content;Merge duplicate rows and sum/average... prevents duplicate cells;compare range...
- Choose duplicate or uniqueALL RIGHT;select a blank row(all cells are blank);Super Find i Fuzzy FindAmong many workbooks; randomly selected...
- exact copymultiple cells without changing the formula reference;Automatically generate referenceson several sheets;insert the bullet, checkboxes, etc.
- Favorite and fast formula insertion, ranges, charts and images;encrypted cellwith password;create a mailing listand send an email...
- extract text, add text, delete by position,remove the space; Create and print subtotals by page;Convert cell content to comments and vice versa...
- super filtr(Save the filter scheme and apply it to other sheets);advanced speciesBy month/week/day, frequency etc;special filterBold, italic...
- Combine workbooks and worksheets;Merge tables based on key columns;Split data into multiple sheets;Batch convert xls, xlsx and pdf...
- Group pivot table byWeek number, day of the week, etc.Show unlocked, locked cellsthrough different colors;Select cells with formulas/names...
Read more... Fully functional 30-day free trial... To buy...
Office Tabs - Introduces a tabbed interface to Office to make things easier
- Enable tabbed editing and reading in Word, Excel, and PowerPoint、Publisher、Access、Visio 和Project。
- Open and create multiple documents in new tabs in the same window instead of new windows.
- Increase your productivity by 50% and save hundreds of mouse clicks a day!
Read more... Fully functional 30-day free trial... To buy...
FAQs
Just remove leading, trailing, extra or all spaces in cells? ›
Removes leading and trailing spaces, such as whitespace and non-breaking space before and after the text. USE TRIMENDS on text strings that you received from another application, and that may have extra spacing before and after the strings.
How do you delete all leading and trailing spaces in all lines? ›- Select lines with leading and/or trailing spaces.
- Press Ctrl + E to center align these lines.
- Keeping the lines selected, press Ctrl + R to do right align. This will remove trailing spaces.
- Keeping the lines selected, press Ctrl + L to do left align. This will remove leading spaces.
- Select the cells (range, entire column or row) where you want to delete extra spaces.
- Click the Trim Spaces button on the Ablebits Data tab.
- Choose one or several options: Remove leading and trailing spaces. ...
- Click the Trim button.
- Press Ctrl + Space to select all cells in a column.
- Press Ctrl + H to open the "Find & Replace" dialog box.
- Press Space bar in the Find What field and make sure the "Replace with" field is empty.
- Click on the "Replace all" button, and then press Ok. Voila! All spaces are removed.
Removes leading and trailing spaces, such as whitespace and non-breaking space before and after the text. USE TRIMENDS on text strings that you received from another application, and that may have extra spacing before and after the strings.
Which function removes leading trailing and repeated spaces? ›Removes leading, trailing, and repeated spaces in text.
Which function will be used to remove only the trailing spaces from a string? ›The Python String strip() method removes leading and trailing characters from a string. The default character to remove is space.
Which function removes all leading whitespace in string? ›The strip() function removes all the spaces including left and right of the string.
What is the formula to remove leading text in Excel? ›1. Combine RIGHT and LEN to Remove the First Character from the Value. Using a combination of RIGHT and LEN is the most suitable way to remove the first character from a cell or from a text string. This formula simply skips the first character from the text provided and returns the rest of the characters.
What are leading and trailing spaces? ›Leading spaces (at the start of the text), Trailing spaces (at the end of the text) and Non-Breaking spaces (prevents line breaks from occurring at a particular point) usually get in the way when we want to perform operations in Excel.
How do I remove leading and trailing spaces in Excel macro? ›
The VBA TRIM function is listed under the text category of VBA functions. When you use it in a VBA code, it removes the leading and trailing spaces from the value supplied. In simple words, if there's a space before and after the value it will remove it and returns the rest of the value in the result.
Which of the following functions automatically remove extra spaces when cleaning data? ›TRIM Function
Using the "TRIM" function can eliminate these excessive blank spaces. Select the data cells with excessive blank spaces and tab spaces. Now, select a new cell adjacent to the first cell.
Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
What method removes all spaces from string? ›striptrailing() method removes the spaces present at the end of the string. replaceall(String regex, String replacement) method replaces all the matched substring with a new string.
How do you remove leading from a string? ›Use the TRIM() function with the LEADING keyword to remove characters at the beginning of a string. TRIM() allows you to remove specific character(s) or space(s) from the beginning, end, or both ends of a string. This function takes the following arguments: An optional keyword that specifies the end(s) to trim.
How do I remove leading line breaks in Excel? ›- Select the cell.
- In the formula bar, click at the start of the second line.
- To remove the line break, press the Backspace key.
- If necessary, type a space character, to separate the words in the cell.
- Press Enter, to complete the formula change.
SQL Server TRIM() Function
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string.
To remove leading and trailing spaces in Java, use the trim() method. This method returns a copy of this string with leading and trailing white space removed, or this string if it has no leading or trailing white space.
How do I remove all leading spaces in regex? ›You can easily trim unnecessary whitespace from the start and the end of a string or the lines in a text file by doing a regex search-and-replace. Search for ^[ \t]+ and replace with nothing to delete leading whitespace (spaces and tabs). Search for [ \t]+$ to trim trailing whitespace.
What removes all leading spaces in a string variable? ›The basic approach is to use the lstrip() function from the inbuilt python string library. The function lstrip() removes any unnecessary spaces that are present on the left of the string. We have similar functions rstrip() and strip(). The function rstrip() removes all the spaces towards the right of the string.
How do I remove leading or trailing whitespace in mysql? ›
The TRIM() function removes leading and trailing spaces from a string.
How to remove leading and trailing spaces in string C#? ›The Trim() method in C# is used to remove any leading and trailing whitespace characters in a string. This means that it removes any whitespace that begins or ends a string.
How to remove leading and trailing characters in mysql? ›Note that to remove the leading spaces from a string, you use the LTRIM() function. And to remove trailing spaces from a string, you use the RTRIM() function.
How do you remove trailing characters from a string? ›In the StringUtils class, we have the methods stripStart() and stripEnd(). They remove leading and trailing characters respectively.
Can you remove extra spaces between words? ›Select the text that you want to change. On the Home tab, click the Font Dialog Box Launcher, and then click the Advanced tab. Note: If you're using Word 2007 the tab is called Character Spacing. In the Spacing box, click Expanded or Condensed, and then specify how much space you want in the By box.
How do I remove extra spaces at the end of a paragraph in Word? ›Excessive white space (space characters as well as tab characters) at the end of text paragraphs can be removed via Find & Replace. In the dialog box (Ctrl+H), use ^w^p in the "Find what" box and ^p in the "Replace with" box. Click the Replace All button.
Why is Word adding spaces between lines? ›Word adds space between paragraphs—whether you want it to or not. If you display paragraph marks, you'll not find any extra paragraph marks. This behavior is part of Word's styling. When you press Enter to create a new paragraph, Word increases the line spacing to mark the change from one paragraph to another.