Text Formula

[Alex O]

Is there an easy (non array) formula that will return only 437399?
BOOK / YEAR : FRC/16: Contract Number : 437399

My current formula is =IF(ISNA(VLOOKUP(E18,Table_Query_from_Dmaster,4,FALSE)),"NIS",VLOOKUP(E18,Table_Query_from_Dmaster,4,FALSE))

Thanks

 

[Joe4]

If it is always the value after the last colon that you want, then this formula should work (for an entry in cell A1):

=TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",100)),100))

 

[Jonmo1]

=IF(ISNA(VLOOKUP(E18,Table_Query_from_Dmaster,4,FALSE)),"NIS",TRIM(RIGHT(SUBSTITUTE(VLOOKUP(E18,Table_Query_from_Dmaster,4,FALSE)," ",REPT(" ",255)),255)))

 

[Alex O]

Perfect!!

Thanks

 

[Jonmo1]

You're welcome.

If you're using XL2007+ you can change your formula structure from
=IF(ISNA(VLOOKUP()),"NIS",VLOOKUP())
to
=IFERROR(VLOOKUP(),"NIS")

 

[Alex O]

Great tip...thanks!