3 formula's to extract names and numbers

[Obewan]

Been working though this and have hit a hurdle (namely with the last two on the list). Can anyone suggest a formula for each column that is exactly the same and extract the names and numbers as shown in columns. So need 3 different formulas - i.e. one for which would answer the info in green:
Column B
Column C
Column D

Book5
	A	B	C	D
1	Data	Name	Number	Box
2	Rug 1PANZA ATTACK	PANZA ATTACK	1	1
3	Rug 2OUR LUNA	OUR LUNA	2	2
4	Rug 3ROMENA CHARM	ROMENA CHARM	3	3
5	Rug 4BIG TIMBER	BIG TIMBER	4	4
6	Rug 5MOON DRAGON	MOON DRAGON	5	5
7	Rug 6PRINCE OF EGYPT	PRINCE OF EGYPT	6	6
8	Rug 7SCOTT PLENTY	SCOTT PLENTY	7	7
9	Rug 8BE CHARMING	BE CHARMING	8	8
10	Rug 9GERO LADS(Box 2)	GERO LADS	9	2
11	Rug 10ZAR ZAR BING(Box 1)	ZAR ZAR BING	10	1
Sheet1

 

[Georgiboy]

Maybe the below will help?

Book1
	A	B	C	D
1	Data	Name	Number	Box
2	Rug 1PANZA ATTACK	PANZA ATTACK	1	1
3	Rug 2OUR LUNA	OUR LUNA	2	2
4	Rug 3ROMENA CHARM	ROMENA CHARM	3	3
5	Rug 4BIG TIMBER	BIG TIMBER	4	4
6	Rug 5MOON DRAGON	MOON DRAGON	5	5
7	Rug 6PRINCE OF EGYPT	PRINCE OF EGYPT	6	6
8	Rug 7SCOTT PLENTY	SCOTT PLENTY	7	7
9	Rug 8BE CHARMING	BE CHARMING	8	8
10	Rug 9GERO LADS(Box 2)	GERO LADS	9	2
11	Rug 10ZAR ZAR BING(Box 1)	ZAR ZAR BING	10	1
Sheet1

Cell Formulas
Range	Formula
B2:D11	B2	=LET(rng,A2:A11, num,--TEXTAFTER(LEFT(rng,BYROW(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},TEXTSPLIT(rng,"(")),0),LAMBDA(x,MAX(x))))," "), name,TEXTSPLIT(TEXTAFTER(rng,num,1),"("), tbox,--TEXTBEFORE(TEXTAFTER(rng,"(Box "),")"), box,IF(ISNA(tbox),num,tbox), HSTACK(name,num,box) )
Dynamic array formulas.

 

[Peter_SSs]

Another option one column at a time

Obewan.xlsm
	A	B	C	D
1	Data	Name	Number	Box
2	Rug 1PANZA ATTACK	PANZA ATTACK	1	1
3	Rug 2OUR LUNA	OUR LUNA	2	2
4	Rug 3ROMENA CHARM	ROMENA CHARM	3	3
5	Rug 4BIG TIMBER	BIG TIMBER	4	4
6	Rug 5MOON DRAGON	MOON DRAGON	5	5
7	Rug 6PRINCE OF EGYPT	PRINCE OF EGYPT	6	6
8	Rug 7SCOTT PLENTY	SCOTT PLENTY	7	7
9	Rug 8BE CHARMING	BE CHARMING	8	8
10	Rug 9GERO LADS(Box 2)	GERO LADS	9	2
11	Rug 10ZAR ZAR BING(Box 1)	ZAR ZAR BING	10	1
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=TEXTAFTER(LEFT(A2,FIND("(",A2&"(")-1),SEQUENCE(10)-1,-1)
C2:C11	C2	=--TEXTAFTER(TEXTBEFORE(A2,B2)," ")
D2:D11	D2	=--IFNA(TEXTAFTER(TEXTBEFORE(A2,")")," ",-1),C2)

.. or if all together

Obewan.xlsm
	A	B	C	D
1	Data	Name	Number	Box
2	Rug 1PANZA ATTACK	PANZA ATTACK	1	1
3	Rug 2OUR LUNA	OUR LUNA	2	2
4	Rug 3ROMENA CHARM	ROMENA CHARM	3	3
5	Rug 4BIG TIMBER	BIG TIMBER	4	4
6	Rug 5MOON DRAGON	MOON DRAGON	5	5
7	Rug 6PRINCE OF EGYPT	PRINCE OF EGYPT	6	6
8	Rug 7SCOTT PLENTY	SCOTT PLENTY	7	7
9	Rug 8BE CHARMING	BE CHARMING	8	8
10	Rug 9GERO LADS(Box 2)	GERO LADS	9	2
11	Rug 10ZAR ZAR BING(Box 1)	ZAR ZAR BING	10	1
Sheet2

Cell Formulas
Range	Formula
B2:D11	B2	=LET(d,A2:A11,nme,TEXTAFTER(LEFT(d,FIND("(",d&"(")-1),SEQUENCE(10)-1,-1),num,--TEXTAFTER(TEXTBEFORE(d,nme)," "),box,--IFNA(TEXTAFTER(TEXTBEFORE(d,")")," ",-1),num),HSTACK(nme,num,box))
Dynamic array formulas.

 

[Obewan]

Maybe the below will help?

Book1
	A	B	C	D
1	Data	Name	Number	Box
2	Rug 1PANZA ATTACK	PANZA ATTACK	1	1
3	Rug 2OUR LUNA	OUR LUNA	2	2
4	Rug 3ROMENA CHARM	ROMENA CHARM	3	3
5	Rug 4BIG TIMBER	BIG TIMBER	4	4
6	Rug 5MOON DRAGON	MOON DRAGON	5	5
7	Rug 6PRINCE OF EGYPT	PRINCE OF EGYPT	6	6
8	Rug 7SCOTT PLENTY	SCOTT PLENTY	7	7
9	Rug 8BE CHARMING	BE CHARMING	8	8
10	Rug 9GERO LADS(Box 2)	GERO LADS	9	2
11	Rug 10ZAR ZAR BING(Box 1)	ZAR ZAR BING	10	1
Sheet1

Cell Formulas
Range	Formula
B2:D11	B2	=LET(rng,A2:A11, num,--TEXTAFTER(LEFT(rng,BYROW(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},TEXTSPLIT(rng,"(")),0),LAMBDA(x,MAX(x))))," "), name,TEXTSPLIT(TEXTAFTER(rng,num,1),"("), tbox,--TEXTBEFORE(TEXTAFTER(rng,"(Box "),")"), box,IF(ISNA(tbox),num,tbox), HSTACK(name,num,box) )
Dynamic array formulas.

That's a bit complicated for me - how do I enter the range or is it possible to have 3 separate formula's for each column?

 

[Georgiboy]

If you wanted to change the range, you would change the 'A2:A11' part.

Post 3 has a three column solution.

 

[Obewan]

I think I am getting SPILL as there is a lot other data in column A I didn't put in the minisheet that's corrupting the result.

 

[Obewan]

If you wanted to change the range, you would change the 'A2:A11' part.

Post 3 has a three column solution.

Just checking that 3 column solution now

 

[Georgiboy]

SPILL error usually means there is data in the way of the formula's spill down area, my solution would require there to be no data in the three columns before you enter the formula. It will then spill down and accross.

Sometimes if you click on the spill formula cell it will show you ith blue lines where it is going to spill to.

 

[Peter_SSs]

I am getting SPILL

Quite likely that you are used to copying the formula down the column, or perhaps down and across.

With the post #2 suggestion and the last suggestion in post #3 you would have nothing at all in the range B2:D11 and put the suggested formula in cell B2 and don't copy it anywhere as it will automatically fill all the other results.

 

[Obewan]

3 column one works perfect!! Thank you!!!