J Jenn1981 Member Jan 27, 2015 #1 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
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
Luke M Excel Ninja Staff member Jan 27, 2015 #3 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.
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.
H Haseeb A Active Member Jan 27, 2015 #4 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)
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)
