Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parsing cell contents (Excel 2000)

    <P ID="edit" class=small>(Edited by teachesms on 16-Dec-04 20:25. )</P>Hi folks, I've been given a mess. Someone has taken a file out of foxpro, saved it as a text file, opened it into Excel and wants me to parse out certain cells in Column D depending on the criteria I'm listing below...I've tried IF's, LEFT, MIDS, RIGHTS etc...til my brain is very tired...got any ideas I'd sure appreciate it...also attaching the file so you can see what I mean. Have inserted comment into for reading problem.

    Problem:

    I am having trouble with a formula to parse out the contents of column D and place results in column E...depending on certain criteria.

    If Column D contains something like (1) LWMAIN (without a trailing space) or (1) LWMAIN (with a trailing space) I want to remove anything to the RIGHT of the acronym (1) LWMAIN and to the LEFT of the acronym LWMAIN itself as well. In this example someone has already done this by hand...leaving a trailing space here or there.

    AND

    .. any other cells that do not meet this criteria must be placed into Column E with no change made.

    I would use the LEFT RIGHT or MID on this, but some cells are like:

    LWMAIN 475
    (1) LWMAIN
    (0) LWMAIN
    (72) LWMAIN
    (2) LWMAIN

    and they may have a space inadvertantly left at the right end of them so the formula has to account for that typo, as well as tons of gibberish which someone already cleaned by hand in each cell with the LWMAIN acronym in it.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Parsing cell contents (Excel 2000)

    If I read your instructions correctly, a cell whose contents include LWMAIN should result in just LWMAIN (without spaces before or after it), otherwise remain unchanged. Is that correct? If not, could you give some concrete examples?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing cell contents (Excel 2000)

    That is correct. In cell E1 I need a formula to extract all LWMAIN from any cell that contains it from column D...whether it has anything to the right or left of it...all other cells remain the same...then I will copy and paste special the values back into column D.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Parsing cell contents (Excel 2000)

    Put this formula in E1:

    =IF(ISERROR(FIND("LWMAIN",D1)),D1,"LWMAIN")

    and fill down. Explanation:
    FIND("LWMAIN",D1) returns a number if D1 contains "LWMAIN", and an error value if not.
    ISERROR(FIND("LWMAIN",D1)) is FALSE if D1 contains "LWMAIN", and TRUE if not.
    The IF function returns D1 unchanged if the condition is TRUE, i.e. if D1 does not contain "LWMAIN".
    And it returns "LWMAIN" if the condition is FALSE, i.e. if D1 contains "LWMAIN".

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parsing cell contents (Excel 2000)

    Hans,

    As Always You are the BEST!!! I'm so glad you and these good folks are here. No way would I ever get by without you. You folks teach us so very much.

    As always, I salute you...
    To your knowledge, patience and the willingness to share it.
    Hoping everyone's Holidays here are the best...you all deserve it soooo much!

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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