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, 2022 in Others by Kithuzzz
• 28,520 points
67 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, 2022 by narikkadan
• 52,760 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, 2022 in Others by narikkadan
• 52,760 points
130 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, 2022 in Others by narikkadan
• 52,760 points
171 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, 2022 in Others by narikkadan
• 52,760 points
122 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, 2022 in Others by narikkadan
• 52,760 points
147 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, 2022 in Others by Edureka
• 13,630 points
211 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, 2022 in Others by Edureka
• 13,630 points
380 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, 2022 in Others by Edureka
• 13,630 points
173 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
158 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, 2022 in Others by narikkadan
• 52,760 points
86 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 52,760 points
1,135 views
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