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

Thread: trim first seven charactors from a string

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    OKC
    Posts
    98
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i have a list like this:

    (01101) LOS ANGELES
    (01102) SAN DIEGO
    (01103) MODESTO

    i want a formula that will trim the numbers in paren. so the list will look like:

    LOS ANGELES
    SAN DIEGO
    MODESTO

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use =MID(A1,9,LEN(A1)), where your text is in cell A1. This will remove the first 7 chrs and the space.

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Eddie,

    If your data is that regular, you can also use Data|Text to Columns, using the Fixed Width option.

    Aladin

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =MID(A1,9,LEN(A1))
    Evening Mudface,

    that's interesting, you ended with just LEN(A1) rather than the difference between the initial text length minus the discarded text

    If you read this Nate, would this make your filename [without path] formula a little easier ?

    Cheers both
    Chris

  5. #5
    Guest

    Default

    CHRIS,

    MUDFACES'S FORMULA WORKED WELL, HOW WOULD YOU HAVE WRITTEN IT? I AM CURIOUS....THANKS ALOT...EDDIE G.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Perhaps something like,

    =RIGHT(A1,(LEN(A1)-7)*(LEN(A1)>7))

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, Chris, how's it going .

    In reply to Anon, I think what Chris is saying is that, normally it would have been written as

    =MID(A1,9,LEN(A1)-9)

    MID just returns all the text from the ninth character onwards.

    Edit: Sorry, talking out me bum there, been a long day

    [ This Message was edited by: Mudface on 2002-02-28 11:38 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    gawwwd

    Eddie, thanks - yeah, I like miudafce's method : I hadn't realised you could end with LEN(x) where it *exceeded* the last arguement in a mid function : I would have assumed it returned an error or a load of blank characters, but it doesn't. So much kudos to Mudface.

    There was a similar posting from Nate about trimming down an =cell("filename",a1) request so that it only contained the filename rather than the whole path. More successfully than I, Nate managed this impressively by (ummmm) locating the "[" character in the full path, and putting it's position into a mid function, with the last part of the MID function counting for n cahracters where n equaled the full length of the cell filename function LESS the number returned from the earlier FIND function (ie counting exactly for the remainder of characters)

    *takes a break*

    My observation (not a criticism or anything) was that maybe that that last bit to determine the remaining characters to count for in the last bit of the MID function might not be neccessary based on Mudface's method per the above

    I am relatively new to the FIND function, so Nate's formula was fab. Ditto Mudface's. It just occured to me they could be "combined" to save a bit of typing.

    Hope this makes sense to anyone who's still reading !

    Chris
    :-Q

    [ This Message was edited by: Chris Davison on 2002-02-28 12:25 ]

    Eddie, here's Nate's thread to which I was reffering.... hope it helps satisfy your curiosity more than I was able to !!

    http://www.mrexcel.com/board/viewtop...ic=864&forum=2



    [ This Message was edited by: Chris Davison on 2002-02-28 12:32 ]

Some videos you may like

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
  •