excel – Extract string before _ or . characters

excel – Extract string before _ or . characters

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.

    min_find_replace

Another way

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

excel – Extract string before _ or . characters

If you intend to do this once, we could use the Text to Columns menu option twice.

First split by _(underscore), then again by .(dot). See below for the first step.

enter

Leave a Reply

Your email address will not be published. Required fields are marked *