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
Or you could just test for numbers
If isnumeric(replace(yourfield,"-", "") then replace(yourfield,"-", "chr(336)") else yourfield
Ian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi there Ian
Thanks for your response!
It seems for your answer to work that the field needs to be entirely numeric? The field is a text field, with some numerical strings that we want separated with the em dash rather than a normal short dash, as per example above. When I use this formula, nothing happens, presumably because the whole field is not numeric?
cheers
KB
Hi there Ian
An example of the entered field content is:
Bio-synthetic valve, c. 1995-2001
I need the dash between the numerical values (ie. between 1995 and 2001) to be an em dash, so I figured the simplest way to do that would be to ask Crystal to turn any dash that comes after two numerals and before 2 numerals (ie. is in between them), to turn that dash into an em dash. The reason why I nominate 2 digits is that there may be cases where the user enters only 2 digits in the date range, so all possibilities are covered if I just search on 2 rather than 4.
Hope that makes sense!
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 |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.