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...
Remove spaces before and after the word in Cell
Using TRIM or SUBSTITUTE to remove leading spaces
Remove blank spaces at front and back. TRIM doesnt work on more than a single space
Trim function not working on trailing space
Why is TRIM not removing spaces in Excel?
How do I remove spaces in Excel?
What is the difference between TRIM and CLEAN in Excel?
I copy text from documents into Excel and when they come through, there will be anywhere between 2 and 10 spaces between words. Is there anything I can do to get rid of those?
Thanks for the help!
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.
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.
I have a sheet of names where there are single space, double space, triple spaces at the beginning of the data. TRIM function only works on single spaces and TEXT TO COLUMN isnt ideal as the spaces vary in different cells. What is the cure.
Edit:Thanks everyone for ur help.