Crystal Reports Formula, Space Delimited

Posted on 2009-04-21
Last Modified: 2012-05-06
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.
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)

    Open in new window


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

    Author Comment

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


    Accepted Solution

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


    LVL 25

    Expert Comment

    Yours is simpler and elegant. Go ahead and use it so..
    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]

     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.


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    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 (, 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…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now