| Author |
Message |
cheryla
Groupie
Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
|
   Topic: Need both Alpha and Numeric info from a field
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.
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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)
|
|
IP Logged |
|
cheryla
Groupie
Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
|
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.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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?
|
|
IP Logged |
|
cheryla
Groupie
Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
|
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.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
|
IP Logged |
|
cheryla
Groupie
Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
|
Posted: 31 Aug 2017 at 8:33am |
|
This has work wonderfully!! Thank you so much you are a lifesaver!!!
|
|
IP Logged |
|
cheryla
Groupie
Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
|
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.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
|
IP Logged |
|
cheryla
Groupie
Joined: 23 Oct 2013
Online Status: Offline
Posts: 51
|
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.
Edited by cheryla - 25 Sep 2017 at 2:33am
|
|
IP Logged |
|
|
|
