Trim help please

[Guest]

How can I trim the last space from text but leave the other spaces intact
ie:- Red Lancer (IRE) Red Lancer(IRE)
Torrent (IRE) Torrent(IRE)

Sir Edward Landseer (FR) Sir Edward Landseer (FR)

I've tried =SUBSTITUTE(A1," ","") but this trims all the spaces out.
Any help will be welcomed.

 

[David McRitchie]

Hi Dave,

You almost have it the
TRIM Worksheet Function

But you might want to use a macro so that you can make the change
in the column or other selected cells.

As opposed to using a worksheet function, using copy, then paste special, values,
before you can eliminate the original cells.

see the TrimALL macro and notes that go with it in
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
More information on installing and using a macro
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Roger Govier]

Hi Dave

Try
=LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND("(",A1),255)

 

[Guest]

Hi David
Thanks for your help, i've tried your TRIMALL Macro you suggested but unable
to make it work. Error message "THAT NAME NOT VALID" when I run the Macro.
Any further assistance you can give me would be appreciated.

 

[Guest]

Hi Roger
Thanks for your help.
Your formula worked perfectly with all the names with (---) brackets but
those without brackets showed "#VALUE!" i'm sorry about that I should have
mentioned that.
Is there a way around this.
Any further help with this problem would be appreciated.
regards
Dave

 

[David McRitchie]

Hi Dave,
I think the error "that name is not valid" but I couldn't find what triggers
such an error.

Are you running other macros okay.
What version of Excel.
Did you modify the macro.

If you are using Excel 95 change xlCalculationAutomatic to xlAutomatic

 

[Roger Govier]

Hi Dave

Try
=IF(ISERROR(FIND("(",A1)),A1,LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND("(",A1),255))

 

[Guest]

Hi David
Thanks again.
I'm running Excel 2003, and the TRIMALL Macro is running in its own workbook.
The only alteration I made to the macro was that I put "Option Explicit"
above the Sub TRIMALL.
regards
Dave

 

[Guest]

Hi Roger
Your a genius !! it works perfectly now.
Thanks once again for your help.
regards
Dave