# Thread: Breaking down a dimensional string (2k7)

1. ## 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)))

2. ## 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!

3. ## Re: Breaking down a dimensional string (2k7)

Try the attached example.

4. ## 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.

5. ## 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. ## 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!

7. ## 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>

8. ## 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. ## Re: Breaking down a dimensional string (2k7)

job security <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

10. ## 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
•