Extract string before "_" or "." characters

Question

I want to extract string before "_" or "." characters. e.g.. My C column can have following values and expected output in E:

C                            E
115415.csv                115415
12345_BOI_CEO.csv         12345

I have a formula for "_" i.e.

IFERROR(LEFT(C10, FIND("_", C10)-1),"")

So I want a formula which would extract string before the character mentioned in a single formula and not separate formula.

Answer 1

It seems you were on the right track. Put one of the following standard formulas in E2,

=REPLACE(C2, MIN(FIND(".", C2&"."), FIND("_", C2&"_")), LEN(C2), TEXT(,))
=LEFT(C2, MIN(FIND(".", C2&"."), FIND("_", C2&"_"))-1)

Standard formula in F2 as,

=MID(C2, IFERROR(FIND("_", C2)+1, 1), FIND(".", C2)-IFERROR(FIND("_", C2)+1, 1))

Fill down as necessary.

    

Answer 2

Another way

=MID(C2,1,MIN(SEARCH(".",C2&"."),SEARCH("_",C2&"_"))-1)