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!
Excel formula to remove space between words in a cell - Stack Overflow
how do i remove extra space from a cell
Remove Space Between Numbers in Excel
Remove space(s) before a numerical value within a cell
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...
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.
Suppose the data is in the B column, write in the C column the formula:
=SUBSTITUTE(B1," ","")
Copy&Paste the formula in the whole C column.
edit: using commas or semicolons as parameters separator depends on your regional settings (I have to use the semicolons). This is weird I think. Thanks to @tocallaghan and @pablete for pointing this out.
It is SUBSTITUTE(B1," ",""), not REPLACE(xx;xx;xx).
Hi,
Welcome to Microsoft Community!
To remove extra spaces from a cell in Excel, you can use the TRIM function, which removes all extra spaces from text except for single spaces between words.
- Suppose your text is in cell A1.
- In another cell, enter this formula: =TRIM(A1)
- Press Enter.
- Copy the result and use Paste Special > Values to replace the original if needed.
In case that TRIM doesn't work (e.g., due to non-breaking spaces), kindly use: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
Whereas:
- CHAR(160) is a non-breaking space.
- CLEAN removes non-printable characters
I hope you find the information above useful. Please let me know how it works. I'll be looking forward to your response.
Best regards,
Phoebe-N - MSFT | Microsoft Community Support Specialist
Hi,
The data in your worksheet may not contain only spaces, but also some invisible characters, so the TRIM or CLEAN function does not work well in your worksheet.
Try this formula:
=--TEXTJOIN("", TRUE, IFERROR(MID(A3, ROW(INDIRECT("1:" & LEN(A3)-3)), 1) + 0, ""))
Paste Only the Values into a new column as before and then format them, for example by adding a thousands separator and increase decimal.
Hope this helps.
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…