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

Crystal Reports Formula, Space Delimited

I need help writing a Crystal Reports formula to strip any numbers after a space.
Then I need a similar formula to strip any numbers before a space.
Here is my example:
I have a PO number that looks similar to one of the following:
12345 1
2315678 23
12569 05

The only thing they have in common are a space dividing out the two sections.
I need the first set of numbers (without the space) in one string and,
I need the last set of number (without the space) in another.

Crystal Reports 11
Thanks to anyone who can help.
0
jpdnorthern
Asked:
jpdnorthern
  • 3
  • 2
1 Solution
 
reb73Commented:
Try -
//First Expression
Dim strArray(2) As String
strArray = Split ({yourfield}, " ")
formula = strArray(1)
 
//Second Expression
Dim strArray(2) As String
strArray = Split ({yourfield}, " ")
formula = strArray(2)

Open in new window

0
 
jpdnorthernAuthor Commented:
reb73, I used your first formula and I get "The remaining text does not appear to be apart of the formula" and the entire formula is highlighted. Here is what I tried:

Dim strArray(2) As String
strArray = Split ({workSLPrint.Custpo}, " ")
formula = strArray(1)

Am I suppose to replace more than the {yourfield} ?
0
 
jpdnorthernAuthor Commented:
reb73, formula strArray do not appear to be usable formulas in Crystal.


Capture.JPG
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
jpdnorthernAuthor Commented:
I seemed to have found a formula used for separating first and last names that works:

Left({workSLPrint.Custpo},Instr({workSLPrint.Custpo},chr(32))-1)

And:
Right({workSLPrint.Custpo},(Length({workSLPrint.Custpo})-(Instr({workSLPrint.Custpo},chr(32)))))
0
 
reb73Commented:
Yours is simpler and elegant. Go ahead and use it so..
0
 
James0628Commented:
FWIW, the formulas that reb73 posted may be OK.  CR has two formula syntaxes, "Basic" and "Crystal".  I don't use Basic syntax, but the formulas that he posted look like Basic syntax.  If you had CR set to use Crystal syntax for the formulas, you would get errors if you tried to use a Basic syntax formula (and vice versa).

 If you wanted (just out of curiosity, if nothing else), you could try his formulas again, making sure that CR is set to use Basic syntax (when you're in the formula editor, there's a dropdown list at the top that lets you choose the syntax).

 Or, here's a slightly modified version of his formulas in Crystal syntax:

Split ({workSLPrint.Custpo}, " ") [1]
 

if InStr ({workSLPrint.Custpo}, " ") > 0 then
  Split ({workSLPrint.Custpo}, " ") [2]
else
  ""


 I added the InStr () to the second formula to avoid getting an error if your field doesn't contain a space.

 Of course you can keep using the formulas you have (with Left () and Right ()).  I doubt that either approach is significantly more or less efficient than the other.  I'd say it's largely a matter of personal preference.  Personally, I find the Split () versions easier to follow.

 IAC, I just wanted to maybe explain why his formulas didn't work (assuming that I'm right and the problem was the formula syntax), and give you another option to consider.

 James
0

Featured Post

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.

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