SUBSTITUTE with 3 choices

[Jack Rabbitt]

Hi,

I found this previous post and it seems to help solve my problem too.

However, it doesnt work when there is a `st` or a `nd` date ( 1st or 2nd )
in the date.

I have changed this SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"th",) to this
SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"st",) which solves the problem, but i
can only get it to work as that one line.

What i need it say is something like...

=SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"th",) OR SUBSTITUTE(MID(A1,FIND("
",A1)+1,99),"st",) OR SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"nd",)

This doesnt work, please may someone assist in getting this put together to
work with either a th st nd.

Thanks very much.

 

[Bob Phillips]

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("
",A1)+1,99),"th",),"st",),"rd",)

HTH

Bob

Hi,

I found this previous post and it seems to help solve my problem too.

However, it doesnt work when there is a `st` or a `nd` date ( 1st or
2nd ) in the date.

I have changed this SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"th",) to this
SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"st",) which solves the problem, but
i can only get it to work as that one line.

What i need it say is something like...

=SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"th",) OR SUBSTITUTE(MID(A1,FIND("
",A1)+1,99),"st",) OR SUBSTITUTE(MID(A1,FIND(" ",A1)+1,99),"nd",)

This doesnt work, please may someone assist in getting this put together
to work with either a th st nd.

Thanks very much.

 

[Jack Rabbitt]

Thanks Bob

That worked, I even managed to work out how to get the `nd` (2nd/22nd) in
too !

Cheers.

Try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("
",A1)+1,99),"th",),"st",),"rd",)

HTH

Bob

 

[Bob Phillips]

Oops, missed that one

Bob

Thanks Bob

That worked, I even managed to work out how to get the `nd` (2nd/22nd)
in too !

Cheers.