# How to Split a Field's Values Where a Character Exists in Crystal Reports

Written by barry kaye
• Share
• Tweet
• Share
• Pin
• Email

Splitting the value of an alphanumeric field at a specific character in Crystal Reports is fairly straightforward. The premise is to split the field into two parts at the numeric position of the desired character. We can capture each part's value in a separate formula using the built-in string functions. The first part will capture the fields first position to the split position. The second part will capture

from the split position +1 to the last position.

Skill level:
Moderately Easy

### Things you need

• Crystal Reports

## Instructions

1. 1

Open a new formula and define string variables. Note: // define comments

stringVar s := 'abcdefghijklmnopqrstuvwxyz'; // s = constant or database field value

stringVar c := 'm'; // c = any character in s to define the split position

numberVar L := length(s); // L = length of s (aka: last position)

numberVar I := instr(s, c); // I = numeric field position of c (ex: m=13)

2. 2

Following the variables of Step 1, define the first split part using the variables as arguments in the built-in String Functions.

// mid takes string (s) from the numeric starting position (1) to ending position (I).

mid(s,1,i)

3. 3

Following the variables of Step 1, define the second split part using the variables as arguments in the built-in String Functions.

// mid takes string (s) from the numeric starting position (i+1) to ending position (L).

mid(s,i+1,L)

4. 4

Decide how to use your defined split parts.

1) Displayed as a carriage returned result at the split position

``````   // CHR(13) is the ASCII for a Carriage Return

mid(s,1,I) + chr(13) + mid(s,i+1,L)
``````

2) Displayed as two separate fields.

``````- Name formula to Part 1 and name a copy as Part 2

- Keep the variable declarations in each formula

- Keep mid(s,1,i) for Part 1

- Keep mid(s,i+1,L) for Part 2

- Place Part 1 and Part 2 on report independently
``````

#### Tips and warnings

• If variables were defined as shared or global, they could be reused without redeclaring in Part 2

### Don't Miss

#### References

• All types
• Articles
• Slideshows
• Videos
##### Sort:
• Most relevant
• Most popular
• Most recent