Solved

Memo field string need to split

Posted on 2014-10-28
9
256 Views
Last Modified: 2014-11-05
In Crystal Report XI R2 I have a memo filed that has text in.
I need to split this text in two fields.  For this I was thinking about creating two formulas.
 Usually in the middle of text there is Chr(13) and Chr(10).  It looks easy to solve this, just use InStr to find its position and then use left or right function to pull out first part of text then second part.
However or but.
Problem is that user entering text sometimes put two times Chr(13),Chr(10),Chr(13),Chr(10) in the middle of text.
Sometimes he is putting chr(160) in too and it looks …..Chr(13),Chr(10),Chr(160),Chr(13),Chr(10)…
Sometimes user start text with Chr(13),Chr(10) or Chr(160)  or he first enters  tab Chr(9) then start to enter text.
I need to remove all from left side(beginning of text e.g.Chr(13),Chr(10),Chr(9),Chr(160)).
I  need text starts with capitalizing first letter of text. I need to find first occurrence of Chr(13),Chr(10) then extract text before that .
Then  in need to remove eventually Chr(13),Chr(10) or Chr(160) or Chr(9) from beginning of second part of text  .For the second part of text I have to cut everything after end of text  as user could put Chr(13),Chr(10),Chr(9) or Chr(160) there too.That second part of text has to go to second formula.
0
Comment
Question by:Taras
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40408980
Try this idea

To get the first string
Create a formula - Testx
Global StringVar Array strArray;
strTest := Replace(strTest,chr(160),'');
strTest := Replace(strTest,chr(9),'');

strArray := Split(strTest,chr(13)+chr(10));
strArray[1]

Open in new window


To get other strings (second string)
EvaluateAfter({@Testx});
Global StringVar Array strArray;
If UBOUND(strArray) >= 2 AND Len(strArray[2]) >= 1 then
    strArray[2]

Open in new window

Third String (if there are multiple chr(13) + chr(10))
EvaluateAfter({@Testx});
Global StringVar Array strArray;
If UBOUND(strArray) >= 3 AND Len(strArray[3]) >= 1 then
    strArray[3]

Open in new window


Just add as many as necessary

mlmcc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 40409028
The first text section always ends with Chr(13)-Chr(10) ?  You can't have just one of those, or Chr(10) first, or a Tab before them, etc.?

 Assuming that it always ends with Chr(13)-Chr(10), possibly followed by other things, here's another approach.

 This formula extracts the first section, and removes any Chr(10), etc. characters that might be at the very beginning:

Local StringVar tmp;

tmp := {your field};

while Left (tmp, 1) in [ ChrW (13), ChrW (10), ChrW (9), ChrW (160) ] do
  tmp := Mid (tmp, 2);

if tmp <> "" then
  Split (tmp, ChrW (13)) [ 1 ]


 This formula extracts the second section (if there is one), and removes any Chr(13), etc. characters that might be at the very beginning of that:

Local StringVar tmp;

tmp := {your field};

while Left (tmp, 1) in [ ChrW (13), ChrW (10), ChrW (9), ChrW (160) ] do
  tmp := Mid (tmp, 2);

if not (ChrW (13) in tmp) then
  ""
else
(
  tmp := Mid (tmp, InStr (tmp, ChrW (13)));
  while Left (tmp, 1) in [ ChrW (13), ChrW (10), ChrW (9), ChrW (160) ] do
    tmp := Mid (tmp, 2);
  tmp
)


 If you could have Chr(13), etc. characters at the end of the second section, that formula does not remove those.  It could.  I just didn't know if they were an issue.

 James
0
 

Author Comment

by:Taras
ID: 40409043
mlmcc

I got this error In formula testx

Global StringVar Array strArray;
StringVar strTest;

strTest := Replace(strTest,chr(160),'');
strTest := Replace(strTest,chr(9),'');

strArray := Split(strTest,chr(13)+chr(10));
strArray[1];----- A subscript must be between 1 and the size of the array.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 35

Expert Comment

by:James0628
ID: 40409121
You'll get that error if the field is empty.  Try replacing the last line with this:

if chr(13)+chr(10) in strTest then
  strArray[1]


 James
0
 

Author Comment

by:Taras
ID: 40409142
I made mistake I did not replace strTest with my table field.
It removed error : A subscript must be.....
Do I still need to put this suggestion as James said?
0
 
LVL 35

Expert Comment

by:James0628
ID: 40409156
You would only need a check like that if the field could be completely empty (not even a space).  If the field will never be empty, you shouldn't need that test, but I don't think there would be any harm in including it anyway, just to play it safe.

 James
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40409193
I would add a line at the top to test for a NULL string.  If the chr(13)chr(10) aren't there the array will still have an element

Global StringVar Array strArray;
 StringVar strTest;

If IsNull({YourField}) then
    ""
Else
(
 strTest := Replace({YourField},chr(160),'');
 strTest := Replace(strTest,chr(9),'');

 strArray := Split(strTest,chr(13)+chr(10));
 strArray[1];
)

Open in new window


mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 40409447
You'll also get an error if the string is empty (not null).  For example, the following will give you an error:

Split ("", ",") [ 1 ]

 James
0
 

Author Closing Comment

by:Taras
ID: 40424159
Thanks a lot
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question