Print Page | Close Window

Help needed on a formula!!

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12674
Printed Date: 02 May 2024 at 7:49am


Topic: Help needed on a formula!!
Posted By: duggie
Subject: Help needed on a formula!!
Date Posted: 22 Mar 2011 at 2:49am
Hi, I need help on splitting up a text field that contains email addresses.  Basically, I have a field which can contain one email address, or multiple email addresses, seperated by a semi colon (;).  Each listing starts with a 4 character prefix and the end of the email addresses is dictated  by a colon character (:).  For example,
 
Xls:Jmoore20@doncaster.com;kperkins20@doncaster.com;mmcgann20@doncaster.com:DON007 Monthly Cons Issue Report
 
Xls:kbrown20@doncaster.com:DON015 Doncasters Monthly Issue Report Maintenance
 
So, the first 4 characters can be ignored, as well as the text after the (:) and where each email is divided by a (;) this could be turned into a carriage return?
 
In order for me to extract all of these email addresses and put it into an excel spreadsheet for marketing purposes, I would like the report to split the addresses so that they appear as follows:
 
mailto:Jmoore20@doncaster.com - Jmoore20@doncaster.com
mailto:Kperkins20@doncaster.com - Kperkins20@doncaster.com
mailto:mmcgann20@doncaster.com - mmcgann20@doncaster.com
 
I have tried using a combination of trim, join, filter formulas but I'm just sending my head into a spin!
 
Any help would be greatly appreciated.
 
Thanks


Replies:
Posted By: Keikoku
Date Posted: 22 Mar 2011 at 3:04am
Approach each step logically.

I have a field which can contain one email address, or multiple email addresses, seperated by a semi colon (;). Each listing starts with a 4 character prefix and the end of the email addresses is dictated by a colon character (:). For example,


-Each e-mail is separated by a semi-colon
-Each contains a 4 character prefix
-list of e-mails ends with a colon

Assuming it is a single text field and it will always be this format, you would do the following in order:

1. get the index of the colon, starting from index t (just after the first colon so you don't count it)
2. take the substring from 4 to the index of the colon - 1 and split on semi-colons, and assign that to a temp array variable
3. for each item in the array, append it to a temp string var and add ChrW(13) to the end of each item.

You should end up with a huge multi-line textbox with one e-mail on each line. Though after trying that, I exported to excel (data-only) and it was still one line. I then tried exporting the entire report and it showed up properly, for which I copied the contents of the box and pasted it in another spreadsheet and it worked fine.




Print Page | Close Window