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

# 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
• 3
• 2
1 Solution

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

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

Capture.JPG
0

Author 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

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

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

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