Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need formula to eliminate values

    I need to eliminate last two characters (-O) appearing in the end of each value, each value is differ in counting but having dashes(-) in the end and in some cases there is no need to eliminate as no dashes appearing.


  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi erfan

    ..highlight the range containing the relevant cells, then press [Ctrl][H] to bring up the 'Find-and-replace' dialog.
    Then enter the -O (in the 'find what' box)
    ..leave the 'replace with' box empty
    ..then click [Replace All] button

    zeddy

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    erfan,

    Another method: If values in column A are the values to be checked then in cell B1 enter the following formula and copy down:

    =IFERROR(IF(SEARCH("-",A1)>0,LEFT(A1,LEN(A1)-2),A1),A1)

    You can then copy column B and Paste as values over column A if you like,

    HTH,
    Maud

    left2.png

  4. #4
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Maud

    but (-) ar twice in cell see below image of data.

    Capture.PNG

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Perhaps:

    =IF(LEFT(RIGHT(A1,2),1)="-",LEFT(A1,LEN(A1)-2),A1)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    New Lounger
    Join Date
    Jun 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •