[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Split and Trim Formula Help in Crystal Reports

Posted on 2013-01-17
Medium Priority
Last Modified: 2013-01-22
On an earler question, mlmcc helped me to split some information in a report.  I'm trying to use the same formula in a similar report and come across some errors.  

I have a list of part numbers and descriptions.  They describe a thread diameter and length.  My formula is as follows:
Local StringVar strForValue;
Local StringVar Array strLengthList;
Local StringVar Array strFractionList;

strForValue := Trim(Split(Split({p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);

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

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

I will get an error in the formula in a couple of different places.  One would highlight the very last phrase (strFractionList [2]);  and another would highlight up in the beginning of the formula where it says {p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);

First off:  Could someone explain to me what this formula is doing?  Maybe then I could figure out which "Descriptions" in my data it does not like.  

Secondly:  Does it matter if the description has a lower case "x" eventhough the formula calls for an uppercase "X"?  

If I understood the formula I may be able to change the descripton on some of these parts.

What I end up doing is running the report and going page by page until it causes an alert then I look at the page it stopped on and try to find funky looking descriptions and either filter them out of edit the description.

For instance, I know that it bombs out when it reads this description:  1 1/2-8(UNJ)X63.5DE STUD 4340 COLD ROLL  but if I put a space before and after the "X" it will read fine.  So I understand the it requires this but I'm not sure of anything else that I have to have.

I have figured out that the formula doesn't like when my description is in feet and inches.  For example  5/8-11 X 20'-0" DBL END STUD 4 &10 TOE would bomb.  So is there a way to include this in my formula?

Naturally, it will also bomb if there is not a diameter and length like in "5" SUPPORT STUD" so I am filtering them out individually.  Again, if there is a way to eliminate this in the formula that would be great because now I have to go page my page to try and find one of these.  Currently my report is only for fiscal year 2013 which start 10/1/2012 so I don't have a lot of data.  But I want to remove this filter and have several years worth of data so going page by page won't be an option.
Question by:ITworks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
LVL 101

Assisted Solution

mlmcc earned 500 total points
ID: 38790207
Trim(Split(Split({p21_view_inv_mast.item_desc}," X ", -1,1)[2]," ")[1]);

Split({p21_view_inv_mast.item_desc}," X ", -1,1)[2]
This splits the string into parts using the ' X ' as the split term.  Notice the spaces around the X.  That is why you have a problem if the spaces aren't there.  The -1 says to return all substrings.  The 1 says use a comparison that is NOT case sensitive so an x or X will match.

It then returns the second string found.

The returned string is then split on the spaces and the first part returned which is then trimmed of any spaces it might have at the start or end.

Replaces the inch " notation with nothing

Replace(strForValue,'-',' ');
Replaces the DASH - with nothing

You can replace ' with
Replace(strForValue,"'",' ');

Can you provide a good sample of the data in a spreadsheet.  Just need that field.
Also would be helpful to provide the value you want to return from the formula

ALso explain what (UNJ) means and does it affect the value?

LVL 101

Expert Comment

ID: 38790288
If you think it would help, add a link to the previus question.  I can't find it.

LVL 35

Assisted Solution

James0628 earned 1500 total points
ID: 38791987

 FWIW, I think this is it.


 I found it by searching for one of the variable names.  :-)


 You're probably going to need to be able to define what the data can look like (it has these characters or those characters, in whatever combinations or patterns) and how you want to handle each situation.

 Also, I have a slight correction to mlmcc's post.  He said that Replace(strForValue,'-',' ') replaces the dash with nothing, but it's actually replacing it with a space.  And then the string is split at the space.  So, for example, if you had "1-2" after the " X ", the "1-2" would be changed to "1 2", and then split into "1" and "2".

 The problem with "5/8-11 X 20'-0" DBL END STUD 4 &10 TOE" is that the string is split at the " X " to get:
20'-0" DBL END STUD 4 &10 TOE

 That is split at the spaces and the first part is kept, which is:

 The double-quote is removed and the "-" replaced with a space, and then that's split at the space, giving you two strings:

"20'" and "0"

 The single-quote is still after the 20, which is fine, because the Val function will ignore it.

 I believe the error is because at this point the formula sees that there are two strings ("20'" and "0") and assumes that the second one is a fraction and contains a "/", so it splits the second string ("0") using "/" as a delimiter, and gets an error when it tries to use the part after the "/", because there is no "/".

 You could avoid the error by using UBound to make sure that strFractionList has more than one element (so the formula won't try to use the part after the "/" when there is no "/"), but the real question is, what do you want to see from a description like "5/8-11 X 20'-0" DBL END STUD 4 &10 TOE" ?

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 38793334
My main goal is to filter out parts that are less than 2-1/8" diameter and 24" in length.

So I need to display  the diameter and length of these parts.  The diameter will always the first part of the description but as I have found out there are a very small number of cases that the length was not part of the description (there was not an "X" in the description to separate the two)

Sometimes the person will enter something different like "UNJ".  This is a type of thread, different than normal threads.  There are other instances where someone put in [2A], which is another type of thread call out like in the description 5/8-11[2A] X 4"

Would it be possible to put a statement in the formula saying that if it doesn't have a lone "X" to filter those out?  Since it will be a minimal amount of those parts in this report I can live with that.  

The report is being used to find out which jobs we could transfer to another machine if we purchased the machine.  So if a few stragglers are left behind it's not going to be a major deal.

I've attached an Excel spreadsheet with a small sampling of part descriptons and what I would like to see for my LENGTH.
LVL 35

Assisted Solution

James0628 earned 1500 total points
ID: 38796003
The old formula is trying to convert the length into a number.  For example, if you had "5 X 2-1/2", you'd get the number 2.5.

 Do you actually need that, or do you just need to see the part after the " X ", and then you can interpret it for yourself?  Extracting the part of the field that's after the " X " is easy.  It's trying to interpret it and convert it into a number that's tricky, because of the different formats.

 Also, looking at your sample data, the old formula doesn't handle cases where the length includes feet and inches.  It takes the part after the " X ", up to the first space, and tries to convert that to a number (including a possible fraction).  It's looking for something like my example above, "5 X 2-1/2".  But with a description like '1 1/2-6 X 2' 11-1/4" SINGLE END STUD 4"', the part between " X " and the first space is just "2'", so you'd just get the number 2.  The formula completely ignores the '11-1/4"' part.

 If you really want to convert the lengths into a number, that will need to be addressed.  Then there would be the question of what kinds of units you'd use?  For example, what would 2' 11-1/4" be?  2.94 feet?  35.25 inches?

 If it's OK to just see the part of the description that's after the " X ", without trying to convert it into a number, you can just use:

Split ({p21_view_inv_mast.item_desc}," X ", -1, 1)[2]

 That will just show you everything after the " X " or " x ".  Normally you'd want to check to make sure that the field includes " X ", to avoid getting a subscript error, but if you use the record selection formula below, then the report will only include those records, so that won't be a problem.

 As for your question about only including the descriptions that have a single " X ", you could use a record selection formula like this:

UpperCase ({p21_view_inv_mast.item_desc}) like "* X *" and
not (UpperCase ({p21_view_inv_mast.item_desc}) like "* X * X *")

 That says to only include the records that include " X ", but not if they include it twice (or more).  I used UpperCase to also include any records with " x ", instead of " X ".

 Of course this is assuming that you won't have " X " somewhere else in your description.  For example, let's say that you had some kind of multi-pack that included 5 of something and the description was something like "1 1/2-6 X 12 B7 STUD X 5".  That would _not_ be included on the report, because of the second " X " at the end.


Author Comment

ID: 38801103
I guess I don't really have to have it convert everything into a number.  I could do this in Excel as long as I have the info, meaning if it's in feet and inches I would need to know that.

If I get to choose, I'd like to have everything converted to inches.

I used this formula like you said James,  Split ({p21_view_inv_mast.item_desc}," X ", -1, 1)[2]
and wondered if now we could just make another formula to split everything up to a letter.  I just don't know how to write that formula.
LVL 35

Accepted Solution

James0628 earned 1500 total points
ID: 38804512
Looking for the first position for one of a group of characters (eg. letters) is trickier.  The only way I know to do that is to loop through the characters and check them.

 Assuming that you're using a record selection formula like the one from my last post, so that you know that every record will include " X ", the following should give you everything after the first " X " and before the first letter (in upper or lower case) that comes after that:

Local StringVar str;
Local NumberVar i;

str := Split ({p21_view_inv_mast.item_desc}," X ", -1, 1)[2];

i := 1;
while i <= Length (str) and not (UpperCase (str [ i ]) in "A" to "Z") do
  i := i + 1;

Left (str, i - 1)

Open in new window


Author Closing Comment

ID: 38805274
Thanks to both of you, AGAIN, for your help.


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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