Now the TRIM function will work because all of the non-breaking spaces have been replaced by normal spaces and TRIM is able to remove the extra spaces. Was this Blog helpful? Let us know in the Comments below.
If you enjoyed this post check out the related posts below. Excel Combine, Analyse, Report. Sharyn Baines. Sharyn is an expert trainer. Please log in again. The login page will open in a new tab.
After logging in you can close it and return to this page. Share 0. Tweet 0. Fix 1 Excel remove spaces in numbers There's no doubt that the TRIM function in Excel is fabulous, but sometimes it just doesn't work for you.
First of all, let's look at a number with a space. Fix 2 Remove leading and trailing spaces in Excel Now let's talk about extra leading and trailing spaces found within cells containing text. Cells A7 and A10 contain extra leading spaces. Thankfully, these non-breaking spaces can easily be replaced.
The problem we seem to have is that the cell retains the actual formula and not the results. It rather depends on what value is being returned to cell D2. It still does not want to work. D2 is text with leading spaces. The TRIM function has worked b4. I think the file may be corrupt. Will try the variations you sent. Thansk for your assistance. Check the format of the cell. Can you be a bit more sporting and show us what the Value, formula, format and actual result of the offending cell are?
That way we can see what is happening rather than guessing. What are you doing? If you are trying to replicate the problem so that you can understand it, tell us what you have done.
If you have a different problem, you are either posting to the wrong thread, or hijacking a completed thread. Neither is likely to get you the help you are looking for. What else can I do to remove the leading apostrophe? A cell that is formatted as text contains a string of characters.
Because it is formatted as text, it will not be interpreted as a formula, even if it is a valid formula. One of the ways that older spreadsheet programs indicated this was to put an apostrophe at the beginning of the text string. Just as the equals sign indicates a formula, the apostrophe indicated a text string. If you can manually edit the cell to remove the apostrophe, you could apply a formula to remove it, using something like the following:.
It can be very frustrating to manually delete all empty spaces from every cell you pasted. Therefore I prepared a little macro to save you from this trouble. Trim function can only remove regular spaces with asci code So if you are unable to remove spaces with trim function, you are most likely dealing with non-breaking spaces. You can achieve desired trim functionality by using a substitute function inside trim function like shown below formula will trim cell A1 :.
If you select the table you pasted and run this code, you will see that all numbers on the table will be trimmed and formatted as numbers. Meanwhile text values will be untouched. Tags: excel trim macro trim function.
Your email address will not be published. Notify me of follow-up comments by email. Notify me of new posts by email.
0コメント