Separate String into two columns
Posted on 2011-10-17
I have a large report that has a description of some studs. It is currently a string. The description looks like this:
1 1/2-8 X 9-1/4 B7 STUD
1 1/2-8 X 9-1/4 B7 STUD W/2 2H NUTS
1 1/2-8 X 9-3/4 B7 STUD
1 1/4-7 X 18" B7 STUD HDG W/2NUTS & 2 L/
1 1/4-7 X 26" B7 STUD HDG
1 1/2-6 X 50" B7 STUD DRILLED B.E. HDG
1 1/2-6 X 5-1/2 B7 STUD
1-8 X 8 1/2 B7 STUD W/2 2H NUTS XYLAN BL
1-8 X 8 B7 STUD
I need the description broken into two columns. The first column would be the diameter, which is the first part of the description before the "X". I have a formual for that already. I need help with the second column. I need the length of the stud (the part after the "X") And I need that formatted into a number so that I can do some calculations with it.
I failed to mention that when I said I needed a number format, I need it to display any fraction as a decimal too. So the 9-1/4 would show as 9.250
I was able to get some of the data converted to the way I wanted but I'm having issues for the ones that have a fraction in them or have the " sign showing inches. In other words, if it was a whole number, I could get it to work....but I'm lost if it isn't a whole number.