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:

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"!!!

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.

 

[thestranger66]

Hi Everyone,

Was wondering if someone could help me out with a formula related to the contents of this 15 year old thread.

I am looking to modify the above formula so that it removes the offending part of the text string as well as finding it.

For example, the inputs and outputs might look like this:

"q1 Why?" -> "Why?"
"q1 - Why?" -> "Why?"
"q1 : Why?" -> "Why?"

My attempt at this looks like:
=IFERROR(RIGHT(A1,(LEN(A1)-(FIND(" - ",SUBSTITUTE(SUBSTITUTE(A1," : "," - ")," "," - "))))),"")

The problem I'm having is that the =FIND function only returns the beginning position of the offending string portion and doesn't return the length of the statement. In the cases shown above the =FIND portion of the function will return a 3 in each case. I need it to return a 3 is case 1 and a 5 in cases 2 and 3 so that the =RIGHT function completely removes the offending parts of those strings.

Thanks in advance for the help!

 

[mikerickson]

If you want to return everything after the final [space], - or : in a string

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),":"," ")," ",REPT(" ",255)), 255))

If space is not one of your key characters (i.e. if you want to preserve the spaces after the last key character), it becomes more complicated.

 

[thestranger66]

Hi Mikerickson,

Unfortunately, I do need to preserve spaces after the last key character. The cases I provided did not represent the problem correctly. See new cases below:

"q1 Why do you think - this?" ->"Why do you think - this?"
"q1 - Why do you think - this?" ->"Why do you think - this?"
"q1 : Why do you think - this?" ->"Why do you think - this?"

Thanks.

 

[mikerickson]

I'd build this formula with helper columns.
First column, replace the spaces in the original string with a ~ or some other never used character.
Next column, apply a formula like the one in post 8 (using TRIM and spaces) to the first column, getting everything after the last key character.
Last column, replace the ~ in the second column with a space.

Then you could combine all those columns into one monster formula.