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
Other People Are Reading
Things you need
- Crystal Reports
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)
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).
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).
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
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for