Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Breaking down a dimensional string (2k7)

    I have a string that resembles this: 01_1"x.032"x1/16"_5SDFJKHWIHFIS83

    In this string, there is a diameter, thickness, and inner diameter (respectively) that I want to retrieve. The thing is, the 1" could be 1-1/2" and the .032" could be .25" as examples. it can also be other numbers since its in a 6000 line spreadsheet with all different sizes. After looking at the strings I've noticed that there is really only 1 thing constant about it - before every dimension is a "_" and between each is a "x", ended again by a "_". So I've tried my hand at making a formula to do this that I could just drag down. Unfortunately I can only figure out how to do it for numbers that have up to 2 places (e.g. 1" or 14") using: =IF(B4="","",IF(MID(B4,6,1)="x",MID(B4,4,2),MID(B4 ,4,3))) where B4 is the cell that contains said string. This seems like a clumsy way of doing things and I wonder if there isn't a better way to go about it without using a macro.

    Any suggestions?

    Edit: after some more tinkering, FIND() seems to get me closer to where I want to be... using =IF(B4="","",MID(B4,(FIND("x",B4,3)+1),(FIND("x",B 4,8)-FIND("x",B4,3)-1)))
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a dimensional string (2k7)

    =IF(B4="","",MID(B4,(FIND("_",B4,1)+1),(FIND("x",B 4,1)-FIND("_",B4,1)-1))) works great for the first column... still, I'm stuck on the next 2!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Breaking down a dimensional string (2k7)

    Try the attached example.
    Attached Files Attached Files
    Regards
    Don

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

    Re: Breaking down a dimensional string (2k7)

    I'd ask whoever supplied this information to deliver it in a usable format. But if you really have to use this bizarre format, I'd create lots of intermediate formulas. See the attached sample workbook.
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a dimensional string (2k7)

    Here's what I came up with.

    1st position:
    =MID(B4,FIND("_",B4)+1,FIND("x",B4)-FIND("_",B4)-1)

    2nd position:
    =MID(B4,FIND("x",B4)+1,FIND("x",B4,FIND("x",B4)+1)-FIND("x",B4,FIND("x",B4))-1)

    3rd position:
    =MID(B4,FIND("x",B4,FIND("x",B4)+1)+1,FIND("_",B4, FIND("_",B4)+1)-FIND("x",B4,FIND("x",B4)+1)-1)

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a dimensional string (2k7)

    Well, I did it as a favor for a friend and he has no control over the format submitted and we don't have the infrastructure to fix something like that. Anyway, just wanted to say this is what I ended up using:

    Dim 1 =IF(B4="","",MID(B4,(FIND("_",B4,1)+1),(FIND("x",B 4,1)-FIND("_",B4,1)-1)))
    Dim 2 =IF(B4="","",IF(FIND("_",B4,4)>19, MID(B4,(FIND("x",B4,3)+1),(FIND("x",B4,12)-FIND("x",B4,3)-1)), MID(B4,(FIND("x",B4,3)+1),(FIND("x",B4,8)-FIND("x",B4,3)-1))))
    Dim 3 =IF(B4="", "", MID(B4,(FIND("x",B4,9)+1), (FIND("_",B4,5)-FIND("x",B4,9)-1)))

    I was able to get >99% of them... which is good enough for what he wanted to do. but thanks for your efforts!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a dimensional string (2k7)

    I tried both Hans & wdwells idea which both worked even closer to 100% than mine did, as expected <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Breaking down a dimensional string (2k7)

    I always wonder why people insist on supplying data in a virtually unusable format while it must be available to them in a usable format. It seems to be a recurring theme in Woody's Lounge... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Breaking down a dimensional string (2k7)

    job security <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Breaking down a dimensional string (2k7)

    It certainly keeps us busy <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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