Excel TRIM Function Bug

Question

According to the example spreadsheet given, TRIM doesn't seem to function properly. Am I misinterpreting it? In any case, it's quite annoying for me right now since I need to count the number of words in each cell of a spreadsheet, but I can't create a completely functional, reliable formula or VBA User Defined Function to do it because of this problem.

I've searched all around the web and not found a solution.

The link below for an excel worksheet that I've shared via dropbox that illustrates the problem.

Example Worksheet

After clicking the link, you should see the option to Open Within your browser and be able to select Microsoft Excel Online, which will enable you to view what I've previously entered, make changes, etc.

This screenshot of the spreadsheet shows the cells where the number that was returned appears to be incorrect, as it does not correspond to the quantity of leading, trailing, and excess spaces that are actually there, as highlighted in row 10.

Barely, I would anticipate that the figures in row 13 correspond with those in row 10.

Can someone please help me with this?

Answer 1

Line feed (ascii 10), it appears, is treated as a character within a word by Excel's TRIM.

If this bothers you, get rid of them before applying TRIM. By replacing them with the space (ascii 32) character, you can get rid of them.

=TRIM(SUBSTITUTE(A1,CHAR(10)," "))