|
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
| All times are GMT +1. The time now is 05:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com