RTRIM by formula?

[Richard Schollar]

Hi

How could you perform an RTRIM by formula (not VBA) - so remove trailing spaces but not trim any multiple spaces at front/in the middle?

 

[RoryA]

How about:
=LEFT(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>" "),ROW(INDIRECT("1:"&LEN(A1)))))

 

[oldbrewer]

looks like it is the annual moderator and MVP exam........

 

[RoryA]

It's his third attempt now so I thought I'd help out...

 

[Richard Schollar]

Hi Rory

It's good but my data is in B1 not A1. Please provide a working formula.




Seriously, thanks very much

 

[RoryA]

No worries - nice to know you're using Excel for something...

 

[oldbrewer]

this forum is fun as well - I like bantering bosses.........

 

[Domenic]

For what it's worth, here's another possible approach...

=LEFT(A2,FIND("^^",SUBSTITUTE(A2,RIGHT(TRIM(A2),1),"^^",LEN(A2)-LEN(SUBSTITUTE(A2,RIGHT(TRIM(A2),1),"")))))

 

[Domenic]

It's good but my data is in B1 not A1. Please provide a working formula.

Richard, that was hilarious...