Need both Alpha and Numeric info from a field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22436
Printed Date: 23 Apr 2024 at 10:31pm
Topic: Need both Alpha and Numeric info from a field
Posted By: cheryla
Subject: Need both Alpha and Numeric info from a field
Date Posted: 30 Aug 2017 at 8:21am
I have a string field that has both alpha and numeric information in it. I need a formula that will give me the alpha info and another to give me the numeric info. This field is a user defined field so 2 types of info are entered. Below is a sampling of the data.
S99
015
D15
SR21
O-R86
O1
4
D-R366
DR486
AS you can see the info varies. For us the letters mean one thing and the numbers mean something else. I need to make 2 different fields with this data (for use in other formulas).
Also, the numeric data must be able to be summarized somehow. I've tried several different things but nothing has worked so far. This forum has always helped me in the past.I'm hoping it can help again.
Thanks in advance! Let me know if you need more info. I need to get this resolved within the next day or so as it is a report to be used at month's end.
|
Replies:
Posted By: kevlray
Date Posted: 30 Aug 2017 at 12:46pm
You will need two formulas. The first gets the numeric value, the second gets the characters. Replace "test" with your field.
dim i as number
for i =1 to len(test)
if isnumeric(mid(test,i,1)) then
exit for
end if
next i
formula = cdbl(mid(test,i))
for i =1 to len(test)
if isnumeric(mid(test,i,1)) then
exit for
end if
next i
formula = left(test,i-1)
|
Posted By: cheryla
Date Posted: 31 Aug 2017 at 2:51am
I created 2 formulas based on what you gave me. The numeric formula is telling me that "The remaining text does not appear to be part of the formula" and it highlights the entire formula. Also, the alpha formula tells me that in the first line "for i =1... that "a variable name is expected here" for the i. Any ideas on how to resolve these 2 issues?
thanks again.
|
Posted By: kevlray
Date Posted: 31 Aug 2017 at 6:08am
First did you use Basic syntax (I lost my original reply which mention to use Basic syntax)?
I forgot to add the dim i as number on the second formula.
And you did replace the test with your field?
|
Posted By: cheryla
Date Posted: 31 Aug 2017 at 6:17am
Hi again.
I did replace the test with my field. I added the dim as number on the 2nd formula. So now both are saying the same thing about remaining text...
I'm not sure what basic syntax means. Sorry. I am using crystal XI if that helps at all.
|
Posted By: kevlray
Date Posted: 31 Aug 2017 at 7:15am
In the formula editor, you can use Crystal Syntax or Basic Syntax. Look for the dropdown in the tool bar.
|
Posted By: cheryla
Date Posted: 31 Aug 2017 at 8:33am
|
This has work wonderfully!! Thank you so much you are a lifesaver!!!
|
Posted By: cheryla
Date Posted: 14 Sep 2017 at 5:49am
Hi again! As I said this formula worked perfectly. I decided to use it in yet another report that I am working on that basically has the same issues. It's not working so maybe I need to tweak it somehow? Here is a sample of the data in the field (it is also a string field):
EQI#54545-55
15266D
EQI#5206-09
again I need to pull out just the numbers. The error I'm getting this time is "the string is non-numeric".
Thanks again for your help. This formula has already proved invaluable in 4 other reports so I was hoping it would work in this but it didn't.
|
Posted By: kevlray
Date Posted: 18 Sep 2017 at 4:57am
|
The example data you have presented may need to be parsed with a for loop (or something similar). I will see if I have some time later.
|
Posted By: cheryla
Date Posted: 25 Sep 2017 at 2:33am
Hello. Any further info on this? I'm running into a deadline soon. Any help is greatly appreciated.
Thank you.
|
Posted By: cheryla
Date Posted: 04 Oct 2017 at 2:48am
I wondered if you were able to look into to this further. Please advise.
THanks again!!
|
Posted By: kevlray
Date Posted: 04 Oct 2017 at 3:58am
This should work. Replace str with your field.
numbervar i;
stringvar result;
for i := 1 to len(str) do
(if isnumeric(mid(str,i,1)) then
result := result + mid(str,i,1)
);
result;
|
Posted By: cheryla
Date Posted: 06 Oct 2017 at 9:17am
Hi again. Thx so much for responding. However, something still isn't quite right.
I entered in the formula as basic syntax and it's telling me "a statement is expected here" for numbervar
When I tried it as crystal syntax it is taking the very first part # and repeating it:
my first few part #'s are:
10993
11273
11399
11399B
EQI#10831-03
The result I am getting for every line is:
1099310993109931099310993
should there be an end if or something to tell it to stop and the move to the next line?
I know we're close to solving it. I really do appreciate your time.
|
Posted By: kevlray
Date Posted: 06 Oct 2017 at 10:50am
One, it is not basic syntax but crystal syntax. (the := for assignment statements is a easy give away).
I am not sure where you are putting the formula (group header, detail, etc.). You may need another formula to clear out result (group header?). You may need to create another detail line above where you are displaying the result and put the clear formula there (you can suppress that detail line).
i.e., stringvar result := "";
|
|