• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel formula everything before underscore OR a space

J

Jenn1981

Member
Hi Everyone, how are you.
How can I create a formula to give me everything before the first underscore OR Space. Whatever comes first.
So far I have this =LEFT(A10,FIND("_",A10)-1) but I dont know how to include for space.

Thank you so much and have a good day.

jenn
 
We can use the MIN function to figure out which one to take.
=LEFT(A2,MIN(FIND(" ",A2),FIND("_",A2))-1)

Note that this assumes there's a least one space and one underscore in the text.
 
Hello Jenn,

You can add " _" (one space & _ ) at the end. Since FIND will always stop at first occurrence MIN will give lowest.

=LEFT(A10,MIN(FIND({" ","_"},A10&" _"))-1)
 
You must log in or register to reply here.
Back
Top