Skip to Content
author's profile photo Former Member
Former Member

Extracting characters from a string for grouping purposes

A business student of mine has a specific task he needs to execute in Crystal Reports. I'd imagine there are functions that can act together to accomplish this task, or maybe its even easier than i am imagining. Either way, here is his specific issue (pasted below). Any leads as to how to accomplish this would be greatly appreciated.

-Nathan

We need reports were we group by the middle 2 characters like 03 or the last like 122

The database field is an alphanumeric field of 11 characters (text field) we use numbers only like 08523003122 formatted it stand for 085230-03-122

The first 6 characters are always there the middle 2 can be there or 2 spaces the last 3 are there or not

So like 085230 formatted 085230- -

Or like 085230 122 formatted 085230- -122

Or like 08523003 formatted 085230-03-

So database wise we have 11 characters with grouping on position 7,8 and or 9,10,11

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 06, 2013 at 07:00 AM

    You can assign the field to a string array then use the instr(),mid() and left() or right() to get the numbers at position 7-8 or 9-10-11th and create a group based on this formula.

    If you can attach a sample excel dump, someone from us might share the exact formula.

    btw: what is preferred if both7-8 and 9-10-11 exist in a string

    -Prathamesh

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 06, 2013 at 07:15 AM

    Hi Nathan,

    If you're simply trying to format the numbers in a specific format then create a formula with the code below:

    stringvar s := {database_field}; //Replace {database_field} with the alphanumeric field

    If len(s) = 6 then
    Left(s,6)&"-"&" "&"-"
    else if len(s) = 8 then
    Left(s,6)&"-"&Mid(s,7,2)
    else if len(s) = 11 then
    Left(s,6)&"-"&Mid(s,7,2)&"-"&Right(s,3);

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.