=trim() should work Answer from a_gallon_of_pcp on reddit.com
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
Top answer 1 of 12
36
=trim() should work
2 of 12
5
So we know what we're dealing with can you try =CODE(MID(B2,SEQUENCE(1,LEN(B2)),1)) and fill down and then show us the result?
DataCamp
datacamp.com › tutorial › how-to-remove-spaces-in-excel
How to Remove Spaces in Excel: 5 Good Methods | DataCamp
March 4, 2026 - This removes spaces and ensures Excel treats the data as numbers. Combine VALUE() and TRIM() to remove spaces before numbers.
Remove space(s) before a numerical value within a cell
Live Online Excel Training for Your Team from MrExcel. Bring MrExcel to your people. Two hours. High-impact. Zero travel. Click here for details. If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password. ... 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 ... More on mrexcel.com
18
0
August 1, 2011excel tricks to remove spaces at the beginning and ending of the cells?
Dear experts, I would like to seek your support on how to remove the spaces containing at the beginning and ending of some cells in a range? I already knew that we can use Conditional Formatting to find out and color the target cells but - how… More on learn.microsoft.com
4
0
removing blank spaces before numbers
Download Microsoft Edge More info about Internet Explorer and Microsoft Edge ... I have an excel spreadsheet that was never correctly formatted. Many of the row and columns have numbers with a variety of spaces (not uniform) before the numbers. I want to remove the spaces before the numbers ... More on learn.microsoft.com
5
0
May 22, 2019How to remove a single Leading space in the numeric column in Excel 2013 - Stack Overflow
My exact problem is: I've few numeric ... into excel. In those columns there is a SINGLE Leading space at the beginning of each number in the cell of the entire column. Example 523946.00. In this number there is a single space before the digit 5. I tried a lot with TRIM and SUBSTITUTE in-built functions but nothing able to resolve my problem of removing ... More on stackoverflow.com
02:31
Remove blank spaces before and after text in EXCEL - YouTube
03:05
How to Remove Space before numbers in Excel - YouTube
01:18
How to Remove Space Between Numbers in Excel | How to Remove Unwanted ...
03:20
How to Remove Spaces From Numbers in Excel - Office 365 - YouTube
01:38
Excel Tips - Removes Extra Spaces | TRIM Function - YouTube
Excel Insider
excelinsider.com › home › our blog › excel pro tips › how to remove space before numbers in excel (5 suitable ways)
How to Remove Space Before Numbers in Excel (5 Suitable Ways) - Excel Insider
February 3, 2026 - Learn how to remove space before numbers in Excel using Find & Replace, TRIM, MID & FIND, Data Validation, or VBA for clean data.
ExcelDemy
exceldemy.com › home › excel basics › how to remove a space before numbers in excel (3 easy ways)
How to Remove a Space before Numbers in Excel (3 Easy Ways) - ExcelDemy
May 3, 2024 - NOTE: Here, I have copied the Product Codes into the Correct Code column for better representation. Press Ctrl + H on your keyboard to open the Find and Replace option. In the Find What option, enter a Space.
The Bricks
thebricks.com › home › resources › how to remove spaces in excel before numbers
How to Remove Spaces in Excel Before Numbers
April 22, 2025 - Why Do Spaces Before Numbers Cause Problems in Excel?Method 1: Using the Find and Replace FeatureMethod 2: The TRIM Function - A Cleaner’s Best FriendMethod 3: The VALUE and TRIM Combo for Bulletproof ConversionMethod 4: A Clever Trick with "Paste Special Multiply"Method 5: Automating the Cleanup with Power QueryWhat if the Spaces Aren't "Normal" Spaces?Final Thoughts
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.
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 - Select all cells with Trim formulas (B2:B8 in this example), and press Ctrl+C to copy them. Select all cells with the original data (A2:A8), and press Ctrl+Alt+V, then V. It is the paste values shortcut that applies the Paste Special > Values · Press the Enter key. Done! As you have just seen, the Excel TRIM function removed all extra spaces from a column of text data without a hitch. But what if your data is numbers, not text?
WPS Office
wps.com › academy › how-to-remove-space-before-text-in-excel-quick-tutorials-1864698
6 Easy Ways to Remove Leading Spaces in Excel [2026] | WPS Office Academy
March 27, 2025 - Method-1 How to use "TRIM Function" to remove spaces in Excel Method-2 How to use the "Find and Replace" tool to remove leading spaces in Excel Method-3: How to use "Data Validation" to remove spaces before data in Excel Method-4: How to use "Trim formula" to remove space before Text Method-5: How to use "Trim formula" to remove space before the number Method-6: How to use "VBA" to remove space before the number The Functionality of Trim and The Find and Delete Method WPS Office - Free Alternative to Microsoft Office to Remove Spaces in Excel FAQs about removing leading spaces in Excel Summary
Top answer 1 of 4
1
Re: have removed spaces but some are still there.
Importing data can bring in unwanted spaces and other characters.
The 'non breaking space', character 160, is one of them.
From 10 years ago...
Using Replace:
Select the column or columns with data in it,
Press CTRL+H to bring up the Replace dialog box.
In the "Find what" field, type ALT+0160 using only the Number Pad to type in the digits.
To be safe: type a single space character into the "Replace with" field
Next, click the "Options" button to reveal all the available options and make sure
the check box labeled "Match entire cell contents" does not have a check mark in it.
Finally, click the "Replace All" button.
'---
Or you can select one of the left over spaces and paste it into the Replace dialog box.
Or you could use a formula =SUBSTITUTE(D1,CHAR(160),"")
Or you could avail yourself of my free 'Professional Compare' workbook, which has a "Clean Data" utility. It provides several options. Download from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
'---
Nothing Left to Lose
2 of 4
0
.... Excel
tricksto remove spaces at thebeginningandendingof the cells?
One of my very first vba codes added to the toolbar was a code to clean input data. On each cell, use Replace to remove Chr(160), followed by using the function "Clean", and then "Trim".
Vba's TRIM() function does what you want: Removes Spaces at the beginning and end. The worksheet TRIM is slightly different because it also removes extra spaces in the middle.
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 - Similarly, you can remove all spaces in your data by typing a single space in the Find what box, and then nothing in the Replace with box. This usually comes in handy when you have sets of numbers or dates that don’t need spaces at all.
Microsoft Learn
learn.microsoft.com › en-us › answers › questions › 5022740 › removing-blank-spaces-before-numbers
removing blank spaces before numbers - Microsoft Q&A
May 22, 2019 - If it doesn’t help, can you please provide the problematic Excel workbook by uploading it to OneDrive and share us the link to access it. It helps us to resolve from our end. Appreciate your understanding. ... You may refer to my solution here - Remove space resembling characters from a range of cells. Hope this helps. ... Please let us know the result at your convenience. ... You might try highlighting the cells, and then click on "Convert to Number", as shown in this screen-print:
Spreadsheet Planet
spreadsheetplanet.com › home › how to remove space before text in excel
How to Remove Space before Text in Excel (5 Easy Ways)
May 12, 2026 - But if you have single-word text strings (such as first name only or email address) or numbers and you want to get rid of all the space characters, you can use this method. You can also use the above steps to remove all the double space characters while leaving the single space characters intact. To do that, you need to insert two space characters instead of one single space character in Step 3. Also read: How to Remove First Character in Excel? Using VBA macro code is another easy way to quickly remove space characters before the text in Excel.
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. This method will not delete extra space at the beginning or end of the value, just spaces between characters.
YouTube
youtube.com › watch
Remove Space Between Numbers In Excel - YouTube
*Learn how to remove space between numbers in Excel quickly and easily.* If you're dealing with messy data — like phone numbers, account numbers, or values t...
Published August 6, 2025 Views 16K
Unstop
unstop.com › home › blog › remove spaces in excel: key methods to tackle different types
Remove Spaces In Excel: Key Methods To Tackle Different Types
February 3, 2025 - To address this, use the SUBSTITUTE function to replace spaces with no characters. For example, =SUBSTITUTE(A1, " ", "") will convert a number formatted with spaces into a usable numeric value, ensuring accuracy in calculations like SUM or AVERAGE.
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
The easiest way to get rid of excess spaces is using the standard Excel Find & Replace option: Press Ctrl + Space to select all cells in a column. Press Ctrl + H to open the "Find & Replace" dialog box.
Published April 21, 2016 Views 76K
Top answer 1 of 5
3
Edit:
You can actually just use find and replace.
- Copy one of the trouble cells.
- Select all the cells containing non break space, and select find and replace.
- Paste the copied cell into the find bar, delete everything but the last character (asuming that is the non breaking space).
- Leave the replace bar empty and press replace all.
This removes all non breaking spaces. :)
**Old Solution:**You can add nothing with paste special to the whole column where the spaces occur.
First copy an completely empty cell. (! remember this step)
Then select all cells in the column and right click and select paste special.
Then select "add" almost at the bottom (see picture) and press ok.
This will make excel reevaluate the values as if you had modified in and entered the value manually. Excel then correctly converts them to numbers. :)
2 of 5
2
First make sure you have the column Formatted as you would like. Make sure it is a number with 2 decimal places (or how ever many you need), then also make sure that there is no Indents (Maybe you think the Indent is a space?) And that you have it Aligned to the Left, Or where you want the Data To be. This alone should take care of your issue.
If that doesn't work here a list of possible solutions.
=Value(Trim(A1)) ' Removes all white space before and after the text in A1
=Value(Clean(A1)) 'Removes all non printable Charactersin A1
=Value(SUBSTITUTE(I3," ","")) 'Substitutes(Replaces) all instances of " "(Space) with ""(nothing)
'****Note: With Substitute you can also specify how many
' Substitutes(Replaces) to make of the value
=Value(SUBSTITUTE(I3," ","",1)) ' Same as above but with only remove the FIRST space
=Value(Trim(Clean(A1))) ' Removes all white space before and after the text
' after removing all Non-Printable Characters
=Value(Trim(Clean(Substitute(A1," ","")))) ' Removes all white space before and after the
'after removing all Non-Printable Characters
' And after replaceing all spaces with nothing
=Value(Right(A1, Len(A1)-1)) ' This takes the End of your text by the number of characters
' in the value Except the First (In your case should be the Space)
=Value(SUBSTITUTE(I6,CHAR(160),"")) 'To help with the non breaking spaces also.
If nothing works could you please share Why you would like to remove the space? As in what you are trying to do with the data? As maybe that will open more solutions
With Ole Henrik Skogstrøm's Suggestion added Value around the functions to get the result as a value.
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...
MrExcel
mrexcel.com › forums › question forums › excel questions
How to remove the spaces before and after the numbers | MrExcel Message Board
June 28, 2018 - Hi there, We could always remove the first and last character of the given string like this: =LEFT(MID(A1,2,LEN(A1)),LEN(A1)-2) This formula will return the string without the first character on the left and the first character on the right, ...