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
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
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...
excel tricks to remove spaces at the beginning and ending of the cells?
Trim spaces without formula?
trim spaces from excel - Apple Community
Remove space at beginning of cell
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
.... 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.
Hey everyone! Wondering if someone can help; I'm seeing lots of references to a Trim Spaces button in older versions of Excel, but it's nowhere to be found in the new one and shunting data around between columns and using formulas is gonna be a drag on this much data. Is there seriously no way to do this without formulas any more? Thanks!!