# Mid Find function with 2 variables

#### trankim

##### Board Regular
Hi,
I am trying to create a formula that will search for either a ">" or "_" and return the text after the 1st "_" or ">". In other words, how can I combine the below two formulas so that it searches for either "_" or ">".

formulas:
=MID(\$Z5,FIND(">",\$Z5)+1,LEN(\$Z5))
=MID(\$Z5,FIND("_",\$Z5)+1,LEN(\$Z5))

Text Examples:
us_Sales_Forms_test_en should return Sales_Forms_test_en
US>Sales>>>Forms>>survey should return "Sales>>>Forms>>survey

Thank you for any help that you can provide.

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Maybe something like that

=MID(\$Z5,1+MIN(FIND({"_";">"},\$Z5&"_"&">")),LEN(\$Z5))

M.

Thank YOU! It worked beautifully!!!!

Or better

=IF(OR(ISNUMBER(FIND("_",Z5)),ISNUMBER(FIND(">",Z5))),MID(A1,1+MIN(FIND({"_";">"},Z5&"_"&">")),LEN(Z5)),Z5)

M.

Thank YOU! It worked beautifully!!!!

You are welcome and thanks for the feedback

M.

That worked too when I changed the "A1" in your formula to "Z5", but forgive me if I don't understand the difference between the two and why the 2nd one is better? I would think the first one since it's shorter? I appreciate your thoughts for learning purposes.

One more for you to consider...

=REPLACE(\$Z5,1,FIND("_",SUBSTITUTE(\$Z5,">","_")&"_"),"")

That worked too when I changed the "A1" in your formula to "Z5", but forgive me if I don't understand the difference between the two and why the 2nd one is better? I would think the first one since it's shorter? I appreciate your thoughts for learning purposes.

If the text has neither "_" nor ">" the first formula returns "" (blank)

The second returns the original text.

M.
ps: thank you for changing A1 to Z5 - i was making tests using A1 and forgot to change when i posted.

Last edited:

Replies
7
Views
497
Replies
14
Views
236
Replies
6
Views
610
Replies
9
Views
613
Replies
1
Views
305

1,203,605
Messages
6,056,231
Members
444,852
Latest member
MJaspering

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back