Extract name from cell

[Josu]

Basically I am confused, not sure if this is even possible
For example we have such text in cell A1

TEST/1of3, to get test I can use formula =LEFT(A1,FIND("/",A1)-1) But this cell A1 can look also TEST 1of3, and formula returns #Value error. Is it possible to make formula to check this 2 conditions, and give in the end this "test"(can be much more characters longer) as result? I need extract text before /1 of 3 or xx 1 of 3

 

[kevin9999]

You may need to enter with Ctrl+Shift+Enter in Excel 2010.

Book1
	A	B
1	TEST/1of3	TEST
2	TEST 1of3	TEST
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(A1," ","|",1),"/","|",1))-1)
B2	B2	=LEFT(A2,FIND("|",SUBSTITUTE(SUBSTITUTE(A2," ","|",1),"/","|",1))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Josu]

You may need to enter with Ctrl+Shift+Enter in Excel 2010.

Book1
	A	B
1	TEST/1of3	TEST
2	TEST 1of3	TEST
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(A1," ","|",1),"/","|",1))-1)
B2	B2	=LEFT(A2,FIND("|",SUBSTITUTE(SUBSTITUTE(A2," ","|",1),"/","|",1))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks a lot, any chance to have more symbols added to this formula? like for example "-", or "\", I am a bit dumbo, when I try to change give me too many arguments error

 

[kevin9999]

Like this..

Book1
	A	B
1	TEST/1of3	TEST
2	TEST 1of3	TEST
3	TEST-1of3	TEST
4	TEST\1of3	TEST
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","|",1),"/","|",1),"-","|"),"\","|"))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Josu]

Like this..

Book1
	A	B
1	TEST/1of3	TEST
2	TEST 1of3	TEST
3	TEST-1of3	TEST
4	TEST\1of3	TEST
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=LEFT(A1,FIND("|",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","|",1),"/","|",1),"-","|"),"\","|"))-1)
Press CTRL+SHIFT+ENTER to enter array formulas.

So complicated, cant track where is start, where is end
Thank you very much!

 

[kevin9999]

So complicated, cant track where is start, where is end
Thank you very much!

Happy to help and thanks for the feedback