SUBSTITUTE with 3 choices

  • Thread starter Thread starter Jack Rabbitt
  • Start date Start date
J

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.
 
Try

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

HTH

Bob

Jack Rabbitt said:
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.
Click to expand...
 
Thanks Bob

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

Cheers.


Bob Phillips said:
Try

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

HTH

Bob
Click to expand...
 
Back
Top