Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Need Excel formula to delete the last word in a cell

This is a discussion on Need Excel formula to delete the last word in a cell within the Excel Questions forums, part of the Question Forums category; Hello, This seems so easy, but I can't get it to work correctly. If a cell or group of cells ...

  1. #1
    New Member
    Join Date
    Dec 2003
    Posts
    10

    Default Need Excel formula to delete the last word in a cell

    Hello,

    This seems so easy, but I can't get it to work correctly.
    If a cell or group of cells contains a product description, and the last word is undesired in all the cells, how would we go about this? I've found this formula to delete the first word:

    =RIGHT(A1,LEN(A1)-FIND(" ", A1,1))

    before:
    This is a test.

    after
    is a test.

    What is desired is:
    This is a

    Thank you.

  2. #2
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Need Excel formula to delete the last word in a cell

    Pretty neat reference

    http://www.mrexcel.com/board2/viewto...last+word+cell

    Should be cross-applicable with the code there, so you can get it to do what you want.

    "Don't Ruin an Apology with an Excuse"...

  3. #3
    Board Regular
    Join Date
    Dec 2002
    Posts
    346

    Default Re: Need Excel formula to delete the last word in a cell

    try chg right to "left"

  4. #4
    New Member
    Join Date
    Dec 2003
    Posts
    10

    Default

    Thank you santeria, I'll check it out.
    2rrs, I tried "left" and it works, but not correctly, for example.

    Cell A1 contains:

    This is a very long test C340409

    Using "left", will return the results:

    This is a very long test C34

    The formula only deletes 5 characters back.

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Location
    Tempe, Arizona
    Posts
    323

    Default Re: Need Excel formula to delete the last word in a cell

    Quote Originally Posted by jhillas
    Hello,

    I've found this formula to delete the first word:

    =RIGHT(A1,LEN(A1)-FIND(" ", A1,1))

    This is a

    Thank you.
    This formula will work if the last space is in the last six characters of the entry. If the last space is more than six characters from the end then you would have to modify this formula. Maybe it will give you some idea how to proceed.

    =LEFT(A1,FIND(" ",A1,LEN(A1)-6))
    Started building macros in Lotus 123, then Quattro Pro and now program in Excel VBA.

    Gaynard Nelson

    The race is not always to the swift
    But to those who keep on running

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,929

    Default Re: Need Excel formula to delete the last word in a cell

    =TRIM(LEFT(A1,LEN(A1)-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

  7. #7
    MrExcel MVP
    Moderator
    Smitty's Avatar
    Join Date
    May 2003
    Location
    Crested Butte, CO
    Posts
    26,834

    Default Re: Need Excel formula to delete the last word in a cell

    These examples might help you:

    http://www.cpearson.com/excel/FirstLast.htm

    HTH,

    Smitty

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Need Excel formula to delete the last word in a cell

    ******** ******************** ************************************************************************>
    Microsoft Excel - y031211h1.xls___Running: xl97 : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    This*is*a*test.This*is*a**
    Sheet8*

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    The formula in cell B1 is ...

    =LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    Board Regular santeria's Avatar
    Join Date
    Oct 2003
    Location
    Tallahassee
    Posts
    1,844

    Default Re: Need Excel formula to delete the last word in a cell

    Very Handy Link...

    Much appreciated.






    Quote Originally Posted by pennysaver
    These examples might help you:

    http://www.cpearson.com/excel/FirstLast.htm

    HTH,

    Smitty
    "Don't Ruin an Apology with an Excuse"...

  10. #10
    New Member
    Join Date
    Dec 2003
    Posts
    10

    Default

    Thank you all.

    Yogi,

    That formula is exactly what I'm looking for, once again, thank you.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com