Results 1 to 9 of 9

Remove last word in a string...

This is a discussion on Remove last word in a string... within the Excel Questions forums, part of the Question Forums category; I need to remove the last word of a cell/string. This should be simple but I am braindead at the ...

  1. #1
    New Member
    Join Date
    Feb 2004
    Posts
    15

    Default Remove last word in a string...

    I need to remove the last word of a cell/string. This should be simple but I am braindead at the moment. Thanks for your help.

    I have this:

    Billy Bob's Law Office 700, Corporate Ste 201 ,


    I need this:

    Billy Bob's Law Office 700, Corporate Ste 201

    So, basically I need to clip the comma off the end. I can't use a subsitute because it will replace all the commas. Thanks again.

    Aaron

  2. #2
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Remove last word in a string...

    Welcome to the Board -- to clarify, you want the last comma and everything after it to be gone?

    EDIT -- If so, then --

    ******** ******************** ************************************************************************>
    Microsoft Excel - FOO3.xls___Running: xl2000 : OS = Windows Windows 2000
    (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
    Billy*Bob's*Law*Office*700,*Corporate*Ste*201*,*This*City*ST***
    2
    Billy*Bob's*Law*Office*700,*Corporate*Ste*201****
    Sheet3*

    [HtmlMaker 2.40] 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.


    A2: =LEFT(A1,FIND(CHAR(127),SUBSTITUTE(A1,",",CHAR(127),LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1)
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  3. #3
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,453

    Default

    one way, i'm sure there's better...

    =MID(CellRef,1,45)
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  4. #4
    Board Regular indiantrix's Avatar
    Join Date
    Oct 2002
    Location
    Philadelphia 'Burbs
    Posts
    943

    Default Re: Remove last word in a string...

    If all your strings end in a space and a comma, then this should do the trick =left(A1,len(a1)-2)
    Or, alternatively, =TRIM(left(A1,len(A1)-1))
    Hope this addresses your problem sufficiently...
    /s/Larry

  5. #5
    New Member
    Join Date
    Feb 2004
    Posts
    15

    Default Re: Remove last word in a string...

    Thanks for the help, but the problem is that the lengths of the strings change (there are about 700 addresses where I need to clip the comma off the end). Im sorry, I forgot to mention that in the original post. Thanks

    Aaron

  6. #6
    New Member
    Join Date
    Feb 2004
    Posts
    15

    Default Re: Remove last word in a string...

    Larry,

    It does not seem to be working correctly. It just returns the same value.

  7. #7
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,453

    Default

    try...

    =LEFT(A10,LEN(A10)-2)

    ...where A10 houses content.
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  8. #8
    New Member
    Join Date
    Feb 2004
    Posts
    15

    Default Re: Remove last word in a string...

    THANKS just_jon!

    Your formula works great, I only needed to remove the comma, but I will use this formula as a learning tool for future reference. Thanks for your help!

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

    Default Re: Remove last word in a string...

    Hi Sharkbot:

    How about ...

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book2___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
    **********************
    2
    Billy*Bob's*Law*Office*700,*Corporate*Ste*201*,*Billy*Bob's*Law*Office*700,*Corporate*Ste*201****
    3
    ****
    Sheet5*

    [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.
    Regards!

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

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