excel tricks to remove spaces at the beginning and ending of the cells?

Question

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 can we remove them from a selected range?
Somehow I used replace the space with nothing, but it didn't really work fine, some problematic cells didn't get detected by the Replace action.

Any advice would be much appreciated.

Answer 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

Answer 2

Hi @Eaven HUANG ,

Did you want you remove blank from cells?
If yes, you can refer to this article:

https://www.extendoffice.com/documents/excel/3313-excel-find-and-replace-blank-cells.html#a1

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

If my understanding is incorrect, please be a bit more precise to explain your requirement or you can upload a screenshot so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.