Trim help please

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.

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave" <[email protected]> wrote in message news:[email protected]...
> 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.

Hi Dave

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

--
Regards

Roger Govier


"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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.

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.

"David McRitchie" wrote:

> 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
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Dave" <[email protected]> wrote in message news:[email protected]...
> > 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.
>
>
>

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

"Roger Govier" wrote:

> Hi Dave
>
> Try
> =LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND("(",A1),255)
>
> --
> Regards
>
> Roger Govier
>
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave" <[email protected]> wrote in message news:[email protected]...
> 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.
>
> "David McRitchie" wrote:
>
> > 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
> > ---
> > HTH,
> > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> >
> > "Dave" <[email protected]> wrote in message news:[email protected]...
> > > 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.
> >
> >
> >

Hi Dave

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


--
Regards

Roger Govier


"Dave" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Roger Govier" wrote:
>
>> Hi Dave
>>
>> Try
>> =LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND("(",A1),255)
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Dave" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

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

"David McRitchie" wrote:

> 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
>
>
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
>
> "Dave" <[email protected]> wrote in message news:[email protected]...
> > 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.
> >
> > "David McRitchie" wrote:
> >
> > > 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
> > > ---
> > > HTH,
> > > David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
> > > My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
> > > Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
> > >
> > > "Dave" <[email protected]> wrote in message news:[email protected]...
> > > > 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.
> > >
> > >
> > >
>
>
>

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

"Roger Govier" wrote:

> Hi Dave
>
> Try
> =IF(ISERROR(FIND("(",A1)),A1,LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND("(",A1),255))
>
>
> --
> Regards
>
> Roger Govier
>
>
> "Dave" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Roger Govier" wrote:
> >
> >> Hi Dave
> >>
> >> Try
> >> =LEFT(A1,FIND("(",A1)-2)&MID(A1,FIND("(",A1),255)
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Dave" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>