Results 1 to 7 of 7

Remove text from cell

This is a discussion on Remove text from cell within the Excel Questions forums, part of the Question Forums category; I saw an answer to a query posted earlier which is almost the same as a problem I have. I ...

  1. #1
    Ben
    Ben is offline
    New Member
    Join Date
    May 2002
    Posts
    37

    Default Remove text from cell

    I saw an answer to a query posted earlier which is almost the same as a problem I have.
    I have text in a cell that looks as follows: The rows go down about 1000 rows.

    ABCD_YZ1234_123456789

    _ is a space.

    After the second space there will always be 9 digits.
    I want to be able to strip those last 9 digits and the second space out of the string leaving only the remaining text to the left.

    The mid section e.g. YZ1234 could be variable up to a between 3 and 6 characters. This means i can not just use LEFT(A1,9) as I will not always get the variable mid section.
    the spaces will always be the same.

    A previous post offered

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

    This I have been tweaking to try to sort my problem but I can't quite nail it.
    Any ideas?

    Thanks in advance

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,678

    Default

    If you download & install the morefunc.xll add-in, you can have...

    =SUBSTITUTE(A1," "&WMID(A1,WORDCOUNT(A1)),"")

    Otherwise, use the generic...

    =SUBSTITUTE(A1," "&RIGHT(A1,LEN(A1)-SEARCH("@#",SUBSTITUTE(A1," ","@#",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"")
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Ben
    Ben is offline
    New Member
    Join Date
    May 2002
    Posts
    37

    Default

    Aladin
    Thanks - Option 2 worked a treat. Thanks for your assistance

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    910

    Default

    =LEFT(A1,LEN(A1)-10)

    or am I missing something.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    390

    Default

    Quote Originally Posted by inarbeth
    =LEFT(A1,(LEN(A1)-10))
    Or, without the first space:
    =SUBSTITUTE(LEFT(A1,LEN(A1)-10)," ","",1)

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

    Default

    Quote Originally Posted by inarbeth
    =LEFT(A1,LEN(A1)-10)

    or am I missing something.
    You're not missing anything. I opted for a generic solution, meaning "no preset constant(s) in the formula".

  7. #7
    Ben
    Ben is offline
    New Member
    Join Date
    May 2002
    Posts
    37

    Default

    Thanks all for your help the LEFT LEN calculation does the business as well.

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