Solved

# Crystal Reports Formula, Space Delimited

Posted on 2009-04-21
1,078 Views
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
Question by:jpdnorthern

LVL 25

Expert Comment

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

Author Comment

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

Author Comment

reb73, formula strArray do not appear to be usable formulas in Crystal.

Capture.JPG
0

Accepted Solution

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

LVL 25

Expert Comment

Yours is simpler and elegant. Go ahead and use it so..
0

LVL 34

Expert Comment

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

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 …
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…