Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
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
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
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