Memo field string need to split

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.
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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
James0628Commented:
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
TarasAuthor Commented:
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.
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

James0628Commented:
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
TarasAuthor Commented:
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?
James0628Commented:
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
mlmccCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James0628Commented:
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
TarasAuthor Commented:
Thanks a lot
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.