Excel TRIM Function Bug

0 votes

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?

Nov 5 in Others by Kithuzzz
• 20,660 points
32 views

1 answer to this question.

0 votes

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)," "))
answered Nov 5 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25 in Others by narikkadan
• 37,660 points
67 views
0 votes
1 answer

Excel OFFSET function with a dynamic row argument

The answer is to make "anchor cells" ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
86 views
0 votes
1 answer

Equivalent of Excel Round function

Here's a sample LINQPad program that demonstrates one way ...READ MORE

answered Sep 26 in Others by narikkadan
• 37,660 points
60 views
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
77 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
145 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
248 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
112 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE

answered Feb 18 in Others by gaurav
• 22,040 points
77 views
0 votes
1 answer

Excel trim function is removing spaces in middle of text - this was unexpected (?)

Create a UDF that uses VBA's version ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
48 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP