Basic: extract number from a AlphaNumeric field

[jainaditya6]

Hello Everyone!

I am a "wet-behind-the-ears" newbie to the world of crystal reports (4th day using BO! ).

Here is my question: how do i extract (or mask) numbers from a alphanumeric string. E.g. I have a field in my report which is something like ABC123XYZ, how do i create a formula which would give me 123. In excel, I achieve this by using this formula:

B1=LOOKUP(9^9,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$100))))

where A1 contains the alphanumeric string (ABC123XYZ) and B1(123) the numeric data.

Sorry for the long winded post, but I am really looking towards your assistances.

 

[lbass]

Is the length of the field always the same? Are the numbers always in the same position? Can the numbers be scattered among alpha characters?

Please show some samples that demonstrate the variation in the field, if any.

-LB

 

[jainaditya6]

Sorry for missing out the details, but Yes the length of the field varies, since the number series in the alpha starts from 1 and goes all the way to 1000, but the alpha characters stay same. Yes, the numbers are always at the same position i.e. they are always between 'C' and 'X'(ABC100XYZ). No, the numbers are not scattered among the alpha characters.
Sample variation:
ABC1XYZ
ABC2XYZ
.
.
ABC10XYZ
.
.
ABC100XYZ

I hope this helps. Please bear with me, since this is my first post! and I appreciate, you taking time out to go through my post.

-AJ

 

[CR85user]

Try:

if isnumeric(mid({tbl.string},7,1)) then mid({tbl.string},4,4) else
if isnumeric(mid({tbl.string},6,1)) then mid({tbl.string},4,3) else
if isnumeric(mid({tbl.string},5,1)) then mid({tbl.string},4,2) else
if isnumeric(mid({tbl.string},4,1)) then mid({tbl.string},4,1) else ''

Replace {tbl.string} with your field.

I'm sure there is a more elegant way to approach it - but this would be the 1st thought that springs to mind.

The formula is checking for the central number being 4 digits - if so return all 4 digits - then it checks for 3 / 2 / 1 digit samples. If it matches none of the above then it returns a blank value.

'J

 

[CR85user]

By the way - I'm sure you are giving ABC and XYZ as example alpha values only, but if you are being literal then the much simpler extractstring({tble.string},'C','X') would strip out anything between those two characters.

'J