4

I know lots of techniques to remove trailing spaces from cells in Excel (e.g. TRIM), but sometimes I get given large sheets from other people which contain trailing spaces in dangerous places, where it's not obvious there's a problem because the trailing spaces exist in multiple places (so lookups etc still work) - but those trailing spaces cause unexpected problems later.

In Word and various publishing applications, there are options to show invisible characters. For example, in Word, it's called "Show non-printing characters", and it looks like this:

enter image description here

And text editors often show invisible characters in selected text, for example from Sublime Text 2:

enter image description here

Is there any kind of setting in Excel whereby invisible characters like trailing spaces could become visible? I'm not fussy about how they become visible, just that they do, across the whole sheet (i.e. not just in one selected cell).

1
  • 1
    See this answer for an example of how to TRIM() all cells: stackoverflow.com/a/21016303/4138090 I don't think the option that you'd like exists but this will likely solve your problem better anyway. It will skip any cells which have a formula so it should be fairly benign.
    – krowe2
    Apr 15, 2015 at 14:37

4 Answers 4

3

1: Use a conditional format with the whole table selected and a formula to set the condition, I used

=OR(RIGHT(A1,1)=" ",LEFT(A1,1)=" ")

and all cells with a leading or trailing space got a red background and are double underlined, this will let you focus on those cells that have extra invisible spaces and as @fixed1234 suggested the underlines with show you where the spaces are. Cells that Excel treats as numbers do not show up with spaces even if they were entered with spaces (unless preceded by an apostrophe thus forcing it into a text format).

Conditional formats on cells with spaces


2: Find or edit a Font where the space character is not blank.

Some ideas about font editing in the answer linked https://graphicdesign.stackexchange.com/questions/19554/how-to-replace-a-character-in-a-font-with-another-character

4
  • 1
    Your #2 is clever. It would help your answer if you could identify a font for which that was the case. One shortcoming I can see is similar to using underlining alone. The sheet will be full of "legit" spaces, so any trailing ones will be hiding in plain sight. It would require looking at every cell.
    – fixer1234
    Apr 15, 2015 at 19:28
  • 1
    Ooh, nice! I love the conditional formatting idea. I tried it on a sheet that had been causing me problems and it worked perfectly! I've put a question on the design site asking is there any font where white-space or non-printing characters are visible, for proofing - if there is, I'll have the conditional format swap it in in place of the underlining Apr 16, 2015 at 8:47
  • That formula doesn't seem to work for ` 11/19/2018` (notice leading space). I am not sure why. but I first selected the cells, then added the conditional formula. What am I doing wrong?
    – J86
    May 17, 2019 at 11:35
  • Note that =TRIM(A1)<>A1 (a) is shorter, and (b) will also catch values with multiple consecutive spaces, like “abc      def” (which might also cause problems; i.e., values that look equal will not be treated as equal). Jun 22, 2021 at 20:24
3

The best answer I've found (Rick Rothstein, MVP - Excel):

If I understand what you want to do, then unlike Microsoft Word (which allows you to do what you are asking for), Excel does not have a way of showing you the non-printing characters I think you are interested in. One reason may be because what you see in the cell may not be "real"... it could come from, or be a concatenation from, other cells on the sheet, or from a different sheet or even from a different workbook. On top of that, the cell may be formatted to look the way you see it, but the actual value in the cell may be completely different. I think it would be a monumental task for the programmers of Excel to figure out how to show you the "invisible characters" you seek given the various ways in how the value displayed in the cell could be formed to look that way.

1
  • 3
    Hmm, that's what I feared. If I was an Excel product designer, I'd add an option to have cells with no background colour slightly grey and then areas with text white, so you could see a white-on-grey block where there are trailing spaces Apr 15, 2015 at 14:50
2

Two solutions:

  1. Conditional formatting

    This method doesn't directly show the invisible characters, but it identifies cell that have them. Select a cell and go to Conditional Formatting. Select the option to use a formula. Say the cell is A1. Enter a formula like =LEN(TRIM(A1))<LEN(A1) and select a fill color. Use Copy, then select the entire sheet, and Paste Special | format to copy the conditional formatting. Any cell containing trailing spaces will be highlighted.

  2. Underlining

    This solution will identify the trailing spaces but is not nearly as useful as conditional formatting because with everything underlined, you need to look at each cell to see if it is revealing anything. Select the entire sheet, go to the font format window (right-click | format cell | fonts, or Fonts from the ribbon menu), and turn on underlining (turn off when you're done). The underline will extend to the end of the content, so trailing spaces will be identifiable.

2
  • 1
    From Microsoft website we get "The TRIM function removes spaces from text except for single spaces between words." It would give a false positive if there were no leading or trailing spaces but there were a sequence of multiple spaces. I like the underline and this can be used with the conditional format.
    – KalleMP
    Feb 5, 2017 at 20:25
  • Why bother with LEN? =TRIM(A1)<>A1 (a) is shorter, and (b) will catch the same problematic values. Jun 22, 2021 at 20:24
0

Using Ctrl+U (underscore) usually helps me to see all spaces in words

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.