Results 1 to 10 of 10

20080225, 20:54 #1
 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 11/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>

20080225, 21:06 #2
 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>

20080225, 22:06 #3
 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.
Regards
Don

20080225, 22:10 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20080225, 22:14 #5
 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)

20080225, 22:20 #6
 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>

20080225, 22:24 #7
 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>

20080225, 22:24 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>

20080225, 22:25 #9
 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>

20080225, 22:27 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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>