Remove text from cell

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Remove text from cell

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,443
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    New Member
    Join Date
    May 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    or am I missing something.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,443
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    New Member
    Join Date
    May 2002
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

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