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

0 votes

It was surprising (?) that the Excel trim function would remove spaces in the middle of text. I mistakenly believed that the excel trim function was used to remove leading and trailing spaces. For instance, "Last Obs Resp" becomes the value of a cell with the value =Trim("Last Obs Resp")

Sure enough, Microsoft documents it this way: https://support.office.com/en-gb/article/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9

I am used to the Oracle database trim function which only removes leading and trailing spaces. https://www.techonthenet.com/oracle/functions/trim.php

Was the excel Trim function always this way?

Excel does not have ltrim and rtrim functions.. i.e. I can't do: =RTRIM(Ltrim("Last Obs Resp"))

I wonder how I achieve the equivalent in Excel when I don't want to remove doubled-up spaces in the middle of the string.

Oct 17, 2022 in Others by Kithuzzz
• 38,010 points
687 views

1 answer to this question.

0 votes

Create a UDF that uses VBA's version of Trim which does not touch the inner spaces. Only removing the leading and trailing spaces

Function MyTrim(str As String) As String
    MyTrim = Trim(str)
End Function

Then you can call it from the worksheet:

=MyTrim(A1)

If you want a formula to do it:

=MID(LEFT(A1,AGGREGATE(14,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)))/(MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1))),1)<>" "),1)),AGGREGATE(15,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)))/(MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1))),1)<>" "),1),999)

enter image description here

answered Oct 18, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Is there a function in excel to automatically calculate age using date of birth?

Try  =INT((YEARFRAC(TODAY(),B3,1)))  Where cell B3 contains a date like ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,010 points
411 views
0 votes
1 answer

Is there a maximum number of formula fields allowed in Excel (2010)

See http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx for limits on specs it doesn't indicate ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 63,720 points
555 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,720 points
1,740 views
0 votes
1 answer

Is there a function to unhide columns in excel through python

Excel file : df.to_excel('demofile.xlsx',index=False) import openpyxl py = openpyxl.load_workbook('demofile.xlsx') exlsheet = ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,720 points
1,910 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,670 points
2,429 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,670 points
1,674 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,670 points
818 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
• 23,260 points
2,979 views
0 votes
1 answer

MAX function in Excel: is it possible to provide the range by means of variables?

Try this: =MAX(INDEX(A:A,B2):INDEX(A:A,B3)) READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,720 points
422 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
• 63,720 points
3,339 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