Search Text for Value and Display a Value

Hi All,

I have a spread sheet with a series of staff numbers (e.g 11234134), at the end of some of the numbers, there can be either -2, -3, -4, -5, -6 (e.g. 1123134-3)

I want to create a formula which will look at the number and if the number has -2 return 'Leave 2' if not 'Leave 1', or if it is -3 return 'Leave 3' if not 'Leave 1' and so on, except for -6, i want it to return 'Leave 1'

Could any one advise how i could go about this?

Thanks,

Luke.

Thanks for the reply. Unfortunately it doesn't seem to do what i need.

I need the formula to return a different value depending on the number. So if the number ends in -4, return 'Leave 4', if it ends in -2, return 'Leave 2'. If the number does not contain a '-' then return 'Leave 1'

Thanks again for your help

Sample.xlsx

Thanks once again for another reply, but this doesn't quite fill all the requirements.
I've attached a sample of what i need the formula for.
Depending on the number, the Leave type will be different.
I've tried what i can, but nothing seems to be able to do it.

Really stuck with this one.

Appreciate any help.

Anyone know a way to do this?

Your sample file doesn't conform to your explanation in your original post.
You said in Post #1 that if number ends in -2 then you want "Leave 2" returned
But your sample file shows that you want "Leave 3 Owe" returned.

Assuming your original explanation is what you want (regarding the numeric return) then this is an option:

="Leave "&IF(AND(RIGHT(A2,2)*1<-1,RIGHT(A2,2)*1>-6),RIGHT(A2),1)&" Owe"

Thanks for that guys, both of them work. Sorry for any confusion, my sample was incorrect.

Haseeb, would you be able to give me a break down of how your formula works. Will help me in the future if I know how it works.

Genuine thanks to you all, been a great help.