Formula to delete data from a cell

[Obewan]

I want a formula I can drag down in column B to delete all data so the name just appears (i.e. the Rug 1, Rug 2.... etc. gets deleted)

Thanks

Book8
	A	B
1	Data	Answer
2	Rug 1BURN THE EDGE	BURN THE EDGE
3	Rug 2KICKING STONES
4	Rug 3ZAMBORA LOLA
5	Rug 4LIAR FOR HIRE
6	Rug 5VACANT BOX
7	Rug 6KINGSBRAE ANNA
8	Rug 7ALL RUM
9	Rug 8LEON KINGSBRAE
Sheet3

 

[Obewan]

I want a formula I can drag down in column B to delete all data so the name just appears (i.e. the Rug 1, Rug 2.... etc. gets deleted)

Thanks

Book8
	A	B
1	Data	Answer
2	Rug 1BURN THE EDGE	BURN THE EDGE
3	Rug 2KICKING STONES
4	Rug 3ZAMBORA LOLA
5	Rug 4LIAR FOR HIRE
6	Rug 5VACANT BOX
7	Rug 6KINGSBRAE ANNA
8	Rug 7ALL RUM
9	Rug 8LEON KINGSBRAE
Sheet3

Further to this can I also have a formula in column C that identifies the number in columns A = so the answer in column would be 1

 

[Joe4]

Is it always exactly a one digit number, as shown in your examples?

If so, you could use this formula in cell B2:

=MID(A2,FIND(" ",A2,1)+2,LEN(A2))

and this formula in cell C2:

=MID(A2,FIND(" ",A2,1)+1,1)

 

[Peter_SSs]

If the names do not contain any digits but the leading number could be more than one digit this may be an option.

23 09 07.xlsm
	A	B	C
1	Data	Name	Number
2	Rug 1BURN THE EDGE	BURN THE EDGE	1
3	Rug 2KICKING STONES	KICKING STONES	2
4	Rug 3ZAMBORA LOLA	ZAMBORA LOLA	3
5	Rug 4LIAR FOR HIRE	LIAR FOR HIRE	4
6	Rug 5VACANT BOX	VACANT BOX	5
7	Rug 6KINGSBRAE ANNA	KINGSBRAE ANNA	6
8	Rug 7ALL RUM	ALL RUM	7
9	Rug 28LEON KINGSBRAE	LEON KINGSBRAE	28
Obewan

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTAFTER(A2,SEQUENCE(10)-1,-1)
C2:C9	C2	=--TEXTAFTER(TEXTBEFORE(A2,B2)," ")

 

[Joe4]

If the names do not contain any digits but the leading number could be more than one digit this may be an option.

23 09 07.xlsm
	A	B	C
1	Data	Name	Number
2	Rug 1BURN THE EDGE	BURN THE EDGE	1
3	Rug 2KICKING STONES	KICKING STONES	2
4	Rug 3ZAMBORA LOLA	ZAMBORA LOLA	3
5	Rug 4LIAR FOR HIRE	LIAR FOR HIRE	4
6	Rug 5VACANT BOX	VACANT BOX	5
7	Rug 6KINGSBRAE ANNA	KINGSBRAE ANNA	6
8	Rug 7ALL RUM	ALL RUM	7
9	Rug 28LEON KINGSBRAE	LEON KINGSBRAE	28
Obewan

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTAFTER(A2,SEQUENCE(10)-1,-1)
C2:C9	C2	=--TEXTAFTER(TEXTBEFORE(A2,B2)," ")

Peter, I admit, I have not used the SEQUENCE function much, and from what I read, it is usually used in a different manner.
Can you explain the logic behind how you are using it to do what is needed here? I am trying to understand it.
Thanks

 

[Peter_SSs]

Can you explain the logic behind how you are using it to do what is needed here?

SEQUENCE(10) produces the numbers 1,2,3,...,10
So SEQUENCE(10)-1 produces 0,1,2,...,9 (That is, a list of all digits. Could have instead used SEQUENCE(10,,0) to do the same thing)
The -1 final argument of TEXTAFTER says to return the text after the last occurrence of whatever is being used as the delimiter.
Using row 9 of my sample data TEXTAFTER the last occurrence of ..
2 would return "8LEON KINGSBRAE"
8 would return "LEON KINGSBRAE"
all other digits would return #N/A since they are not found in the text
Somehow TEXTAFTER knows to ignore all the #N/A values and to use the text after the 'farthest right' digit found.

 

[Joe4]

SEQUENCE(10) produces the numbers 1,2,3,...,10
So SEQUENCE(10)-1 produces 0,1,2,...,9 (That is, a list of all digits. Could have instead used SEQUENCE(10,,0) to do the same thing)
The -1 final argument of TEXTAFTER says to return the text after the last occurrence of whatever is being used as the delimiter.
Using row 9 of my sample data TEXTAFTER the last occurrence of ..
2 would return "8LEON KINGSBRAE"
8 would return "LEON KINGSBRAE"
all other digits would return #N/A since they are not found in the text
Somehow TEXTAFTER knows to ignore all the #N/A values and to use the text after the 'farthest right' digit found.

Very clever!

 

[Obewan]

If the names do not contain any digits but the leading number could be more than one digit this may be an option.

23 09 07.xlsm
	A	B	C
1	Data	Name	Number
2	Rug 1BURN THE EDGE	BURN THE EDGE	1
3	Rug 2KICKING STONES	KICKING STONES	2
4	Rug 3ZAMBORA LOLA	ZAMBORA LOLA	3
5	Rug 4LIAR FOR HIRE	LIAR FOR HIRE	4
6	Rug 5VACANT BOX	VACANT BOX	5
7	Rug 6KINGSBRAE ANNA	KINGSBRAE ANNA	6
8	Rug 7ALL RUM	ALL RUM	7
9	Rug 28LEON KINGSBRAE	LEON KINGSBRAE	28
Obewan

Cell Formulas
Range	Formula
B2:B9	B2	=TEXTAFTER(A2,SEQUENCE(10)-1,-1)
C2:C9	C2	=--TEXTAFTER(TEXTBEFORE(A2,B2)," ")

Thankyou!! Works awesome!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.