Taking Max value from and alphanumeric field

[#1]

I have an alphanumeric field where I need to take the first four characters and then to take the largest number value.

Field example:             Desired Output

S876987630987                       8

A33027889085F                        3

I have tried to use LEFT(Field,4) and then tried a number of things but to no avail.

Any help would be great

Thanks in advance.

[hector_munoz]

Hi Rishi,

If you take the string starting at position 2 till the end: Right(<field>, Len(<field>) - 1), you could order the result using a MaxString() function.

Regards,

H

[bhargav_bhat · Accepted solution]

Hi Rishi,

Instead of using Left function you can use Mid function to achieve your requirement. Below is the sample code

=Rangemax(mid(FieldName,2,1),mid(Fieldname,3,1),mid(Fieldname,4,1))

Regards,

Bhargav

[#4]

Hi Bhargav

With your solution:

602016000088B returns 2 - Incorrect

Q320151843673 returns 3 - correct

4576000000000 returns 7 - correct

9876000000400 returns 8 - Incorrect

Works for some values

[sushil353]

Hi,

Try this:

=RangeMax(

mid(KeepChar(left('FieldName ',4),0123456789),1,1),

mid(KeepChar(left('FieldName ',4),0123456789),2,1),

mid(KeepChar(left('FieldName ',4),0123456789),3,1),

mid(KeepChar(left('FieldName ',4),0123456789),4,1),

)

HTH

sushil

[#6]

I ended up with this working solution:

rangemax(mid(field,1,1),mid(field,2,1),mid(field,3,1), mid(field,4,1)) as Test,

Thanks all for your replys