?
Solved

Separate String into two columns

Posted on 2011-10-17
10
Medium Priority
?
363 Views
Last Modified: 2012-06-27
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.

0
Comment
Question by:ITworks
  • 6
  • 4
10 Comments
 
LVL 4

Author Comment

by:ITworks
ID: 36980355
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.

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36980862
From your other question you got this as the second column

Formula for 2nd column:
Replace(Split({your_field}," X ")[2]," B7 STUD","")

Try - This is in resoonse to the question today that didn't include the requirement to convert fractions

Formula for 2nd column:
Trim(Replace(Split({your_field}," X ")[2]," B7 STUD*",""))

If that doesn't work
Trim(Split(Split({your_field}," X ")[2]," B7 STUD","")[1])

If you can have 5-1/2 as a length, you won't be able to total it directly.  The numbers will have to be converted.
For the conversion, Will the format always be N-N/N or can you have N N/N
where N are any digits like 16-3/8

How large a denominator?

mlmcc


0
 
LVL 4

Author Comment

by:ITworks
ID: 36981014
Unfortunately some of the numbers will be N-N/N and N N/N.

The denominator will be a maximum of two characters long
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 36981169
I think this will work

mlmcc
Local StringVar strForValue;
Local StringVar Array strLengthList;
Local StringVar Array strFractionList;

strForValue := Trim(Split(Split({YOUR FIELD}," X ")[2]," B7 STUD")[1]);

strForValue := Replace(strForValue,'"','');
strForValue := Replace(strForValue,'-',' ');

strLengthList := Split(strForValue,' ');
If UBound(strLengthList) = 1 then
   Val(strLengthList[1])
Else
(   
   strFractionList := Split(strLengthList[2],'/');
   Val(strLengthList[1]) + Val(strFractionList[1]) / Val(strFractionList[2]);
)

Open in new window

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36981179
If there are chances the data will take other formats or that the data may have bad values then there are some tests that can be added to ensure the values used in calculations are numeric strings.

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
ID: 36981250
It'spossible for the format to be different.  This is a description of a part number in database.  People are supposed to adhere to some policies but since several people (30-40 people) have access to create the description there could be situations where someone enters the data differently.  That is why we sometimes see 5-1/2 and 5 1/2.  I can't go in and change this as there is already history on that part.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 36981290
I meant if there are other formats in use like
   5_1/2
   
Or if there were things like
   5 A1/R4

mlmcc
0
 
LVL 4

Author Comment

by:ITworks
ID: 36981319
NO.  Those types of formats are not used.
0
 
LVL 4

Author Comment

by:ITworks
ID: 36981577
WOW!     Great job!    Thanks so much for your help.
0
 
LVL 4

Author Closing Comment

by:ITworks
ID: 36981581
Great job!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question