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. 

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,000 points
856 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,640 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,000 points
596 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,640 points
827 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,640 points
2,045 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,640 points
2,239 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,690 points
3,930 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,690 points
1,975 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,690 points
970 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
3,637 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,640 points
591 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,640 points
3,700 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