Quite often the issue is a non-breaking space - CHAR(160) - especially from Web text sources -that CLEAN can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
Ron de Bruin has an excellent post on tips for cleaning data here
You can also remove the CHAR(160) directly without a workaround formula by
- Edit .... Replace your selected data,
- in Find What hold
ALTand type0160using the numeric keypad - Leave Replace With as blank and select Replace All
Excel University
excel-university.com › home › blog › how to remove leading spaces in excel
How to Remove Leading Spaces in Excel - Excel University
January 17, 2023 - Use Ctrl + F to pull up the Find dialog box. Select the Replace tab, and in the Find what section, type Ctrl + J. This should display a blinking dot. Note that on Mac, you’ll use Ctrl + Option + Return. Leave the Replace with section empty, click Replace All, and you’ll be all set! Removing leading spaces is one great way to clean data in Excel and make your spreadsheets look professional and consistent.
Top answer 1 of 6
242
Quite often the issue is a non-breaking space - CHAR(160) - especially from Web text sources -that CLEAN can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
Ron de Bruin has an excellent post on tips for cleaning data here
You can also remove the CHAR(160) directly without a workaround formula by
- Edit .... Replace your selected data,
- in Find What hold
ALTand type0160using the numeric keypad - Leave Replace With as blank and select Replace All
2 of 6
142
If you would like to use a formula, the TRIM function will do exactly what you're looking for:
+----+------------+---------------------+
| | A | B |
+----+------------+---------------------+
| 1 | =TRIM(B1) | value to trim here |
+----+------------+---------------------+
So to do the whole column...
1) Insert a column
2) Insert TRIM function pointed at cell you are trying to correct.
3) Copy formula down the page
4) Copy inserted column
5) Paste as "Values"
Should be good to go from there...
Remove spaces before and after the word in Cell
Hi I am looking for a code to remove the spaces before and after the words in cells in column B. Can anyone help me on this Excel file attached in the below link Remoe spaces before and after the word in Cell.xlsx Thanks in advance More on learn.microsoft.com
8
366
Remove space(s) before a numerical value within a cell
Trying to remove the space in front of the number '1' in cell A2. So value in cell A2 is = " 1" TRIM function does not work for removing the space before the 1. Any ideas please? More on mrexcel.com
18
0
August 1, 2011How to remove spaces before numbers
u/Major-Bar388 - Your post was submitted successfully. Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread. Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. Include your Excel version and all other relevant information Failing to follow these steps may result in your post being removed without warning. I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns. More on reddit.com
64
38
November 10, 2024Using TRIM or SUBSTITUTE to remove leading spaces
If you are copying from a web page then you are likely copying a non breaking space or another character I cannot recall. Use =CODE( LEFT( cell_address , 1)) to determine what the leading character is then do a search replace (ctrl+h) on it to get of it en masse More on reddit.com
14
8
February 12, 2019Reddit
reddit.com › r/excel › using trim or substitute to remove leading spaces
r/excel on Reddit: Using TRIM or SUBSTITUTE to remove leading spaces
February 12, 2019 -
I have a list of around 500 numbers that I've pasted into Excel. The problem is that they all have a leading space in them, so Excel is not treating them like numbers.
I have tried using the TRIM and SUBSTITUTE formulas to remove the spaces, then copying that and pasting the VALUE into another cell, but it still has a leading space in it. I cannot figure out how to remove the space without manually deleting it, which there are too many numbers to do that to all of them.
Top answer 1 of 4
5
If you are copying from a web page then you are likely copying a non breaking space or another character I cannot recall. Use =CODE( LEFT( cell_address , 1)) to determine what the leading character is then do a search replace (ctrl+h) on it to get of it en masse
2 of 4
3
IIRC TRIM doesn’t always get rid of non printable characters. You might want to employ CLEAN as well. If a CLEAN wrap still doesn’t cure it, maybe point =CODE(LEFT(cell)) at it, find the character code of the offending character, then you can use =SUBSTITUTE(target,CHAR(thatcodevalue),"")
Microsoft Support
support.microsoft.com › en-us › excel › functions › trim-function
TRIM function | Microsoft Support
Example 3: =TRIM(CLEAN(B5)) combines TRIM and CLEAN functions to remove spaces and clean string. Example 4: Copy the example data in the following table and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter.
Atlassian
atlassian.com › data › databases › how to remove leading and trailing spaces in an entire column in excel google docs
Trimming Spaces in Excel & Google Sheets | Atlassian
Today we’ll examine a few simple ... generally efficient) method for removing both leading and trailing space is to use the TRIM() function....
MrExcel
mrexcel.com › forums › question forums › excel questions
Remove space(s) before a numerical value within a cell | MrExcel Message Board
August 1, 2011 - So value in cell A2 is = " 1" TRIM function does not work for removing the space before the 1. Any ideas please? Click to expand... The offending character may not be a standard char 32 space. What result do you get with this formula: =CODE(A2) That will return the character code of the offending character. Once we know that we'll have a better idea of how to kill it! ... Thank you everybody but it looks like the issue is because my colleague has OLD exel and I have NEW excel...
YouTube
youtube.com › watch
Excel Tips - Removes Extra Spaces | TRIM Function - YouTube
Hey team,This video will help you see why the TRIM function can be so helpful in Excel. I will show you a real-life scenario for how this function can help a...
Published November 5, 2024
Ablebits
ablebits.com › ablebits blog › excel › excel formulas › excel trim function - quick way to remove extra spaces
Excel TRIM function - quick way to remove extra spaces
March 20, 2023 - The following screenshot shows that all leading spaces are gone, while multiple spaces between words are still there: As a finishing touch, replace the original text with the trimmed values, as shown in step 3 of the Trim formula example, and you are good to go! Tip. If you also want to remove spaces from the end of cells, make use of the Trim Spaces tool. There is no obvious Excel formula to remove leading and trailing spaces keeping multiple spaces between words intact.
YouTube
youtube.com › shorts › r7fNq_C-WG8
How to Remove Spaces in Excel Before Text - Remove Leading Spaces Before Name in Excel Cell - YouTube
March 22, 2023 - See how to use TRIM function and Replace in Excel to remove leading spaces and extra text spacing in a cell. Have you ever ended up with a column of names in...
Exceljet
exceljet.net › home › formulas › remove leading and trailing spaces from text
Remove leading and trailing spaces from text - Excel formula | Exceljet
May 11, 2024 - If you need to strip leading and trailing spaces from text in one or more cells, you can use the TRIM function. In the example show, the formula in cell C3 is: =TRIM(B3) Once you've removed extra spaces, you can copy the cells with formulas ...
Microsoft Fabric Community
community.fabric.microsoft.com › t5 › Desktop › Is-it-possible-to-Trim-leading-trailing-spaces-using-transform › td-p › 3230950
Solved: Is it possible to Trim leading/trailing spaces usi... - Microsoft Fabric Community
October 11, 2024 - So I went into Transform Data, used split column ("," as delimiter) & split it into rows so I could put a slicer on a graph, but it's got 2 instances of most incidents. My thinking is that it's not removed the space from the start/end of any that were delimited. Is there a way to resolve this without having to go into the excel file, recreating the split column & using TRIM?
YouTube
youtube.com › watch
Remove blank spaces before and after text in EXCEL - YouTube
Want to know how to remove blank spaces before and after text in Excel? In this video I will show you this great time saver.If you found this video useful, t...
Published April 19, 2024
DataCamp
datacamp.com › tutorial › how-to-remove-spaces-in-excel
How to Remove Spaces in Excel: 5 Good Methods | DataCamp
March 4, 2026 - Learn how to remove leading, trailing, and extra spaces in Excel using TRIM(), SUBSTITUTE(), and CLEAN() for clearer data, plus tips for accurate spreadsheets.
Reddit
reddit.com › r/excel › how to remove spaces before numbers
r/excel on Reddit: How to remove spaces before numbers
November 10, 2024 -
When i copy and paste a table with numbers from an email to excel, there will be spaces before the numbers. Is there an easy formula to remove these spaces (i tried to add an image, but my post was removed)
Edit: thank you all for the support. The following formula solved it
=0+SUBSTITUTE(B2,CHAR(160),"")
Edit 2:
u/semicolonsemicolon & u/Joe3453 deserves credit for recognizing nbsp being the issue.
For curious lurkers: https://en.wikipedia.org/wiki/Non-breaking_space
Wikihow
wikihow.com › computers and electronics › software › office › spreadsheets › microsoft excel › how to remove spaces between numbers & words in excel
How to Remove Spaces Between Numbers & Words in Excel
September 13, 2024 - This method removes any extra spaces between numbers, words, and other characters. To eliminate leading and trailing spaces, see Using the TRIM Function.
ASAP Utilities
asap-utilities.com › asap-utilities-excel-tools-tip.php
ASAP Utilities for Excel - Remove Leading and Trailing Spaces
This is especially useful when cleaning up imported data, fixing lookup mismatches (VLOOKUP, XLOOKUP), or preparing consistent text values for sorting and filtering. By default, you can launch this tool using the Control+Alt+Shift+T shortcut. Click ...
SimpleSheets
simplesheets.co › blog › essential-steps-to-remove-spaces-in-excel-that-you-need-to-know
Essential Steps To Remove Spaces in Excel That You Need To Know
To copy trimmed spaces and removed values in a selected column, right-click and select cut/copy. When pasting, use the "paste special" function and paste the trimmed spaces as values. Note: The Excel TRIM function can remove only the leading spaces of a text string while keeping all spaces in the middle intact.
Ablebits
ablebits.com › ablebits blog › excel › excel formulas › 3 ways to remove spaces between words / numbers in excel cells
3 ways to remove spaces between words / numbers in Excel cells
Leading and trailing spaces will also be trimmed to 1, but will not be removed. Select one or several columns with the data to delete spaces between words. Press Ctrl + H to get the "Find and Replace" dialog box.
Published April 21, 2016 Views 76K