Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    TEXT IN CELL MANIPULATION (excel xp or 2003)

    I am trying to extract certain items from a text within a cell. for instance last name for a cell containing "lastname, first name" or dept # or dept title. I set up a work sheet with some of my formulas to try to achieve this however I could not complete the objective in one formula. Please see attached if anyonel could assist me with this.

    Thanks

    MNN

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: TEXT IN CELL MANIPULATION (excel xp or 2003)

    Do you want one formula to cover all these cases? That would require VBA programming.
    Or is is sufficient to have one formula for comma-separated strings, another for strings with a semi-colon, etc.?

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: TEXT IN CELL MANIPULATION (excel xp or 2003)

    How about
    =TRIM(SUBSTITUTE((RIGHT(E2,(SEARCH(",",E2)-2))),","," "))
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: TEXT IN CELL MANIPULATION (excel xp or 2003)

    I'm a bit rusty on these. Without using your intermediate formulas, in Column H test:

    =IF(ISNUMBER(SEARCH(",",E5)),TRIM(RIGHT(E5,LEN(E5)-SEARCH(",",E5))),TRIM(MID(E5,SEARCH(":",E5)+1,3)))

    and in Column I test:

    =IF(ISNUMBER(VALUE(H5)),TRIM(RIGHT(E5,LEN(E5)-SEARCH(H5,E5)-LEN(H5))),"")

    Warning: I cheated and used the assumption that the numeric department code is always two digits. If that is not so, well, see Hans post! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: TEXT IN CELL MANIPULATION (excel xp or 2003)

    or
    =IF(ISERROR(SEARCH(",",E5)),(TRIM(SUBSTITUTE((MID( E5,(SEARCH(":",E5)),4)),":"," "))),(TRIM(SUBSTITUTE((RIGHT(E5,(SEARCH(",",E5 )-2))),","," "))))

    Nice little problem to wake up my brain before the weekend!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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