Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4998
  • Last Modified:

Split and Trim Formula Help in Crystal Reports

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.
  • 3
  • 3
  • 2
4 Solutions
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?

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


 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" ?

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

ITworksAuthor Commented:
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.
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.

ITworksAuthor Commented:
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.
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

ITworksAuthor Commented:
Thanks to both of you, AGAIN, for your help.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

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.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now