Solved

# Separate String into two columns

Posted on 2011-10-17
Medium Priority
363 Views
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
Question by:ITworks
• 6
• 4

LVL 4

Author Comment

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

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

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

LVL 101

Accepted Solution

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]);
)
``````
0

LVL 101

Expert Comment

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

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

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

ID: 36981319
NO.  Those types of formats are not used.
0

LVL 4

Author Comment

ID: 36981577
WOW!     Great job!    Thanks so much for your help.
0

LVL 4

Author Closing Comment

ID: 36981581
Great job!
0

## Featured Post

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