Multiple criteria in FIND formula.

[Andrew70]

I'm scratching my head over this one.

In a cell, I have this text: XXX-XXX

I want to use the FIND function to determine the position of the '-'.

I can do that OK.

The problem I have is, the character I'm looking for will not always be the same.
It will be either a space, a dash (-) or a slash (/).

Can I use the FIND function with multiple criteria to do this?

(Lotus 123 did this very simply with the #OR# function)

Regards,
Andrew.

[Mark W.]

=FIND("-",SUBSTITUTE(A1,"/","-"))

[Andrew70]

Thanks for trying, but I don't want to alter the original data.

I want a single FIND formula to return 5 if any of the three examples below are tested.

XXXX XXXX, or,
XXXX-XXXX, or,
XXXX/XXXX,

[Mark W.]

On 2002-04-09 07:24, Andrew70 wrote:
Thanks for trying, but I don't want to alter the original data.

I want a single FIND formula to return 5 if any of the three examples below are tested.

XXXX XXXX, or,
XXXX-XXXX, or,
XXXX/XXXX,

=FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"/","-")," ","-"))

This formula doesn't "alter the original data"!!!

[Andrew70]

On 2002-04-09 07:47, Mark W. wrote:

=FIND("-",SUBSTITUTE(SUBSTITUTE(A1,"/","-")," ","-"))

This formula doesn't "alter the original data"!!!

When tried your original solution, it didn't work so I looked up the SUBSTITUTE function in Excel help. When I saw that it is a text replacement command, I assumed you'd misunderstood me.

I've since tried your revised solution and that does work.

Thank you.

[Mark W.]

When tried your original solution, it didn't work so I looked up the SUBSTITUTE function in Excel help. When I saw that it is a text replacement command, I assumed you'd misunderstood me.

I've since tried your revised solution and that does work.

Thank you.

I missed the "It will be either a space..." part of your original specification. It's always good to provide a complete set of test data as you did on your subsequent posting.