If trim isn’t working for you, why not do a simple find and replace of the “space” character. That may well work for you.
Answer from chrismason1518 on community.spiceworks.comI have a column containing numbers. Some rows have numbers with 3 or more spaces. I tried to remove those spaces using trim(), clean(), and Ctrl+h (replace). Nothing worked.
How can I remove those spaces after numbers?
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
Hello,
I copy-pasted data from a database into excel and used the "Text to Columns" function to separate the values.
This is an example of a line I copy-pasted into excel: (2, 0, 0, 0, 262, 0, 0, 0). I used the comma delimiter and every value was brought into a different cell. The problem I'm having is that in the first column every number has a space behind it and I want to remove it.
How it looks in excel
I already tried the following methods:
Method 1: I pressed CTRL+F and went into the tab "Replace" and pressed the space bar once into "Find what". In "Replace with" I didn't type anything. When I pressed the "Replace all" button excel says: "We couldn't find anything to replace..."
Method 2: I inserted a new column next to column A and typed into the first cell: =SUBSTITUTE(A2;" "; ""). The space after the number was not removed.
Anyone has an idea on how to fix this?
Thanks in advance!
If trim isn’t working for you, why not do a simple find and replace of the “space” character. That may well work for you.
I know this is a very basic function but even after searches and a YouTube video, I can’t seem to find how to do this and funny, I’ve done it before…
I need to remove the spaces in a row…I’ve tried following examples of the TRIM function but it just doesn’t seem to be working correctly…
Here’s what the numbers look like:
I have about 280 of these and only need the spaces removed from those numbers all in the same column.
Thanks…
- Highlight the range that you want to modify
- CTRL + F
- Click Replace
- "Find what: " (put a blank space here so it searches for spaces)
- "Replace with: " (leave this blank)
- Click Replace all, and the selected range should search for blank spaces and replace them with nothing
There are two functions you can use for replacing a character in a string: Replace() and Substitute():
Replace()can be used when you know where you want to replace whatever.Substitute()can be used if you know exactly what you want to replace anywhere.
So, in this case:
=SUBSTITUTE(C2," ","")
I believe your imported data includes non-breaking spaces instead of standard spaces. Hence, the formula =SUBSTITUTE(A1," ","") does not work. Instead of using the space bar to type the " " in the SUBSTITUTE formula, try a non-breaking space. You can select this in the Character Map or use keystroke Alt+0160.
EDIT:
Since substituting regular spaces successfully removed the leading and trailing spaces, you should use nested Substitute formulas: one to sub the regular spaces and one to remove the no-break spaces.
=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")
You can copy this formula to try it in your workbook.
Just do a search and replace over a group of cells you have selected, select your column and go to Home and Find and Select. Search for a space, and replace it with nothing.
Hi,
This formula (in cell B2) will get rid of white spaces
=REGEXREPLACE(A2,"[^\S]+","")
Hope this helps.
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
