Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Trim Characters Other Than An Empty Space At The End Of A Cell

In my data, I have some cells that end in " - " (space dash space). I would
like to delete the space dash space if it is the last thing in the cell. I
would like to keep it if it is in the middle. I know that the TRIM function
deletes a trailing space, can I edit it to delete a trailing space dash
space? Here's an example:

Mary - poodle
Theo - beagle
Barney -
Jacob - pug
Schmitty -

So, I want "Mary - poodle" to stay the same, and "Barney - " to become just
"Barney" like this

Mary - poodle
Theo - beagle
Barney
Jacob - pug
Schmitty

Thank you!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Trim Characters Other Than An Empty Space At The End Of A Cell

With data in Column A, try this in B1:

=IF(RIGHT(A1,3)=" - ",LEFT(A1,LEN(A1)-3),A1)

And copy down as needed.

To remove the formulas and leave the data behind, copy Column B,
then Paste Special, Values, <OK, <Esc.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"K8_Dog" wrote in message
...
In my data, I have some cells that end in " - " (space dash space). I
would
like to delete the space dash space if it is the last thing in the cell. I
would like to keep it if it is in the middle. I know that the TRIM
function
deletes a trailing space, can I edit it to delete a trailing space dash
space? Here's an example:

Mary - poodle
Theo - beagle
Barney -
Jacob - pug
Schmitty -

So, I want "Mary - poodle" to stay the same, and "Barney - " to become
just
"Barney" like this

Mary - poodle
Theo - beagle
Barney
Jacob - pug
Schmitty

Thank you!



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Trim Characters Other Than An Empty Space At The End Of A Cell

Put this formula in an adjacent column:

=IF(RIGHT(A1,3)=" - ",LEFT(A1,LEN(A1)-3),A1)

Then copy this down as required.

Hope this helps.

Pete

On Apr 18, 11:31*pm, K8_Dog wrote:
In my data, I have some cells that end in " - " (space dash space). *I would
like to delete the space dash space if it is the last thing in the cell. I
would like to keep it if it is in the middle. I know that the TRIM function
deletes a trailing space, can I edit it to delete a trailing space dash
space? Here's an example:

Mary - poodle
Theo - beagle
Barney -
Jacob - pug
Schmitty -

So, I want "Mary - poodle" to stay the same, and "Barney - " to become just
"Barney" like this

Mary - poodle
Theo - beagle
Barney
Jacob - pug
Schmitty

Thank you!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,091
Default Trim Characters Other Than An Empty Space At The End Of A Cell

If your cells end with " - ", space-dash-space then:

=IF(RIGHT(A1,3)=" - ",LEFT(A1,LEN(A1)-3),A1)

"K8_Dog" wrote in message
...
In my data, I have some cells that end in " - " (space dash space). I
would
like to delete the space dash space if it is the last thing in the cell. I
would like to keep it if it is in the middle. I know that the TRIM
function
deletes a trailing space, can I edit it to delete a trailing space dash
space? Here's an example:

Mary - poodle
Theo - beagle
Barney -
Jacob - pug
Schmitty -

So, I want "Mary - poodle" to stay the same, and "Barney - " to become
just
"Barney" like this

Mary - poodle
Theo - beagle
Barney
Jacob - pug
Schmitty

Thank you!



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Trim Characters Other Than An Empty Space At The End Of A Cell

Thanks everyone,
This solution is perfect. LEN is new to me and so cool!
Thanks,
K8Dog

"Tyro" wrote:

If your cells end with " - ", space-dash-space then:

=IF(RIGHT(A1,3)=" - ",LEFT(A1,LEN(A1)-3),A1)

"K8_Dog" wrote in message
...
In my data, I have some cells that end in " - " (space dash space). I
would
like to delete the space dash space if it is the last thing in the cell. I
would like to keep it if it is in the middle. I know that the TRIM
function
deletes a trailing space, can I edit it to delete a trailing space dash
space? Here's an example:

Mary - poodle
Theo - beagle
Barney -
Jacob - pug
Schmitty -

So, I want "Mary - poodle" to stay the same, and "Barney - " to become
just
"Barney" like this

Mary - poodle
Theo - beagle
Barney
Jacob - pug
Schmitty

Thank you!






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default Trim Characters Other Than An Empty Space At The End Of A Cell

And we appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"K8_Dog" wrote in message
...
Thanks everyone,
This solution is perfect. LEN is new to me and so cool!
Thanks,
K8Dog

"Tyro" wrote:

If your cells end with " - ", space-dash-space then:

=IF(RIGHT(A1,3)=" - ",LEFT(A1,LEN(A1)-3),A1)

"K8_Dog" wrote in message
...
In my data, I have some cells that end in " - " (space dash space). I
would
like to delete the space dash space if it is the last thing in the
cell. I
would like to keep it if it is in the middle. I know that the TRIM
function
deletes a trailing space, can I edit it to delete a trailing space dash
space? Here's an example:

Mary - poodle
Theo - beagle
Barney -
Jacob - pug
Schmitty -

So, I want "Mary - poodle" to stay the same, and "Barney - " to become
just
"Barney" like this

Mary - poodle
Theo - beagle
Barney
Jacob - pug
Schmitty

Thank you!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trim hidden characters? robotman Excel Worksheet Functions 3 October 20th 07 12:00 AM
Count Characters with space in a cell NH Excel Discussion (Misc queries) 5 April 5th 07 05:07 AM
How do you trim characters? Tommi Excel Worksheet Functions 6 January 29th 06 02:38 PM
trim a string by specific number of characters windyoldman Excel Discussion (Misc queries) 2 July 13th 05 01:53 PM
Trim function doesn't clean out ASCII Code 160 (Space) Ronald Dodge Excel Worksheet Functions 6 January 27th 05 04:48 AM


All times are GMT +1. The time now is 03:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"