RIGHT function

[Terri H]

Hi there

In my spreadsheet, I import a block of text from an external source (not Excel) into (in Example 1), column N. I automatically capture the order value shown in the text by using the RIGHT function a couple of columns along. =RIGHT(N138,5)

I have set this to 5 digits, as most of the values are this (for example, £75.80... £79.99... £71.98 and so on). On a few occasions, the order value exceeds this and requires 6 digits (for example, £220.98... £354.25... £256.66 and so on). =RIGHT(R138,6)

Is there a better way to express the formula so that it can capture the last 5 digits if it's 5... and/or the last 6 digits if it's 6 - meaning that I don't have to manually check and correct these few anomalies?

Many thanks
Terri

[AliGW]

Try this:

=MID(A1,FIND("£",A1)+1,99)

EDIT: Sorry, that won't work. Let me think again!

[AliGW]

Try this:

=TRIM(RIGHT(SUBSTITUTE(A1,"£",REPT(" ",99)),99))

[Bo_Ry]

Please try

=-LOOKUP(0,-RIGHT(L6,{5,6}))

or

=-LOOKUP(0,-RIGHT(L6,{3,4,5,6,7}))
to cover more digit

[Terri H]

Hi AliGW

That's perfect! Thank you!

Regards
Terri

[Terri H]

Thank you so much, but have now resolved this!

[AliGW]

You're welcome.