Using mid/len/left/right/find

[Accountant2B]

I need excel formulas that will help me differentiate each piece of these cells. Here are examples of the cells

Elephant12-8Wb
CAT193-45M
I already have the formula to get Elephant and CAT on their own. But then I need formulas to get 12 and 193, 8 and 45, and Wb and M on their own.

Thank you in advance

 

[jasonb75]

Welcome to Mr Excel

Does this help?

Book1
	A	B	C	D
1	Elephant12-8Wb	Elephant	12	8Wb
2	CAT193-45M	CAT	193	45M
3	Bug123-456Mo	Bug	123	456Mo
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
C1:C3	C1	=MID(A1,LEN(B1)+1,FIND("-",A1)-(LEN(B1)+1))
D1:D3	D1	=MID(A1,FIND("-",A1)+1,255)

 

[Accountant2B]

Welcome to Mr Excel

Does this help?

Book1
	A	B	C	D
1	Elephant12-8Wb	Elephant	12	8Wb
2	CAT193-45M	CAT	193	45M
3	Bug123-456Mo	Bug	123	456Mo
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
C1:C3	C1	=MID(A1,LEN(B1)+1,FIND("-",A1)-(LEN(B1)+1))
D1:D3	D1	=MID(A1,FIND("-",A1)+1,255)

Yes!! Thank you!
Is there any way you would know how to get column D into separate values? For example cell D1 to be 8 in one column and Wb in another column?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
3	Elephant12-8Wb	Elephant	12	8	Wb
4	CAT193-45M	CAT	193	45	M
5	Bug1234-567Mo	Bug	1234	567	Mo
Main

Cell Formulas
Range	Formula
B3:B5	B3	=LEFT(A3,MIN(FIND(ROW($1:$10)-1,A3&"0123456789"))-1)
C3:C5	C3	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A3,B3,""),"-",REPT(" ",20)),20))
D3:D5	D3	=LEFT(SUBSTITUTE(A3,B3&C3&"-",""),MIN(SEARCH(CHAR(ROW($65:$90)),SUBSTITUTE(A3,B3&C3&"-","")&CHAR(ROW($65:$90))))-1)
E3:E5	E3	=SUBSTITUTE(A3,B3&C3&"-"&D3,"")

 

[jasonb75]

Like this?

As you're using Excel 2019, the formulas in columns C and D may need array confirming with Ctrl Shift Enter.

edit:- @Fluff, I'm looking at your column D formula and thinking about rows being deleted

Book1
	A	B	C	D	E
1	Elephant12-8Wb	Elephant	12	8	Wb
2	CAT193-45M	CAT	193	45	M
3	Bug123-456Mo	Bug	123	456	Mo
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
C1:C3	C1	=MID(A1,LEN(B1)+1,FIND("-",A1)-(LEN(B1)+1))
D1:D3	D1	=MID(A1,FIND("-",A1)+1,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),0))-FIND("-",A1))
E1:E3	E1	=MID(A1,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),0))+1,255)

 

[Accountant2B]

Thank you both so much!!!!!

 

[Fluff]

Glad we could help & thanks for the feedback.