on 04-24-2018 12:34 AM
Hi there
Here is an example of a line of text: Bio-synthetic valve, c. 1995-2001
In the above statement, I want to replace the short dash that appears between the years, but not the one that appears in the conjoined word. Is there a way I can search out 2 numeric characters followed by short dash followed by 2 numeric characters and replace the short dash with a longer em dash?
Desired end result: Bio-synthetic valve, c. 1995—2001
Any help would be greatly appreciated!
cheers
Karen
Yes, there is a way to do this using a couple of variables and a loop in a formula. It looks something like
StringVar result := ' ';
Local NumberVar strLen := Length({MyTable.MyField});
Local NumberVar i := 1;
While i < strLen do
(
if {MyTable.MyField}[i] <> '-' then
result := result + {MyTable.MyField}[i]
else
if (i < strLen) and IsNumeric({MyTable.MyField}[i-1]) and
IsNumeric ({MyTable.MyField}[i + 1]) then
result := result + chr(336)
else
result := result + {MyTable.MyField}[i]
);
result
Note that "chr(336)" is an emdash in the Arial font. You'll want to check whatever font you're using to determine what character you need to use.
-Dell
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi there Dell
I'm not sure I understand your comment regarding date format. The formula field is not a date field, nor is it made up from an original date field (it is just a conditional formula that spits out text depending on the presence of certain data), so I'm not sure how I can tell Crystal that it is a date, when there are other bits of text in there that do not conform to a date format?
KB
Hi again Dell
Essentially, my vision is a formula that will say:
"If there are two numerals followed by a short dash followed by 2 numerals within {myfield} [string1]), then within [string1], replace the short dash with an em dash".
The text entries are not necessarily uniform with regard to date format, so I don't believe there is any way I can apply any date format rules.
Hope that makes sense!
cheers
Karen
Hi Dell
The formula field ("Label") to which I want to apply the new formula to check for dashes is set up like this:
{Label}
IF "#Label#" in({Object_csv.ObjObjectNotes}) then ExtractString({Object_csv.ObjObjectNotes}, "#Label#", "#End#")
So, quite often the people populating the csv.ObjObjectNotes fields between #Label# and #End# will just use short dashes, but the graphic designer wants long dashes between the dates, so this is what has brought me here to this forum.
An example of the entry in the csv.ObjObjectNotes field:
#Section#Medical implants#Label#Bio-synthetic valve, c. 1995-2001#End#
and desired result in the field with adjusted long dash between numerals...
Desired end result: Bio-synthetic valve, c. 1995—2001
With your guidance, I have set up a formula called "LabelDash" which looks like this (well not quite like this, as I have the same line feed structure as you gave me but this interface strips them out):
{LabelDash}
StringVar result := ' '; Local NumberVar strLen := Length({@Label}); Local NumberVar i := 1; While i < strLen do ( if {@Label}[i] <> '-' then result := result + {@Label}[i] else if (i < strLen) and IsNumeric({@Label}[i-1]) and IsNumeric ({@Label}[i + 1]) then result := result + chr(336) else result := result + {@Label}[i] ); result
...but when I put {LabelDash} into the report, i get the error message "A string field can be at most 65534 characters long."
cheers
Karen
Ok, I think I know what's going on. The ObjObjectNotes field is a blob, clob, or varchar(max) field, which can potentially be longer that Crystal can handle. So, we need to limit the length of the string that we can look at. You could try something like this:
StringVar result := ' ';
StringVar smallString := left({@Label}, 65500);
Local NumberVar strLen := Length(smallString);
Local NumberVar i := 1;
While i < strLen do (
if smallString[i] <> '-' then
result := result + smallString[i]
else
if (i < strLen) and IsNumeric(smallString[i-1]) and IsNumeric(smallString[i + 1]) then
result := result + chr(336)
else
result := result + smallString[i]
);
result
-Dell
The only other thought I have would be to create a stored function in the database that would handle this and then, if you're linking tables in the report, create a SQL Expression that calls the function or, if you've used a command in the report, include the function call in the command's Select.
-Dell
User | Count |
---|---|
80 | |
11 | |
10 | |
8 | |
8 | |
6 | |
6 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.