Formula Required

[HSAR]

Hi team MrExcel looking for a formula which get the desired values and texts from column "A" as i did in row 1.

 

[DanteAmor]

I propose you 3 formulas. The formula for the year is an array formula, Commit this formula using CTRL+SHIFT+ENTER and not just enter by itself.

varios 08jul2020.xlsm
	A	B	C	D	E	F
1	Item	PSA Grade	Year	Set	Card Number	Card Name
2	2002 Pokemon Psa 8.5 Raichu Expedition Holo #25 Nm-mt	8.5	2002		25
3	2003 Pokemon skyridge Psa 8 kabutops Holo #13 Nm-mt	8	2003		13
4	Psa 7 NM EX Holon Phantoms Gyarados Gold Star #102. Maybe PSA 9 or 8	7			102
5	Pokemon neo gen 1st edition holo tegetic 16/111 mint condition psa worthy	worthy
6	2000 pokemon team rocket #10 DARK MACHAMP HOLO PSA 10 GEM MINT 1ST EDITION	10	2000		10
Hoja10

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,SEARCH("Psa",$A2)+4,255)," ",REPT(" ",99)),99)),"")
C2:C6	C2	=IFERROR(IF(LEN(1*MID(A2,MIN(FIND({1,2},A2&"12")),4))<4,"",1*MID(A2,MIN(FIND({1,2},A2&"12")),4)),"")
E2:E6	E2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A2,"."," "),SEARCH("#",$A2)+1,255)," ",REPT(" ",99)),99)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

For the Set and Card Number formulas you can put more examples and explain what the pattern would be to identify that data.

 

[DanteAmor]

Check if the following is what you need:

varios 08jul2020.xlsm
	A	B	C	D	E	F
1	Item	PSA Grade	Year	Set	Card Number	Card Name
2	2002 Pokemon Psa 8.5 Raichu Expedition Holo #25 Nm-mt	8.5	2002	Expedition	25	Raichu
3	2003 Pokemon skyridge Psa 8 kabutops Holo #13 Nm-mt	8	2003	Holo	13	kabutops
4	2000 pokemon psa 9 steelix holo 1st edition #15	9	2000	holo	15	steelix
5	Psa 7 NM EX Holon Phantoms Gyarados Gold Star #102. Maybe PSA 9 or 8	7		EX	102	NM
6	Pokemon neo gen 1st edition holo tegetic 16/111 mint condition psa worthy	worthy
7	1999 hitmonchane #7 holo mint psa 9	9	1999		7
8	2000 pokemon team rocket #10 DARK MACHAMP HOLO PSA 10 GEM MINT 1ST EDITION	10	2000	MINT	10	GEM
Hoja10

Cell Formulas
Range	Formula
B2:B8	B2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,SEARCH("Psa",$A2)+4,255)," ",REPT(" ",99)),99)),"")
C2:C8	C2	=IFERROR(IF(LEN(1*MID(A2,MIN(FIND({1,2},A2&"12")),4))<4,"",1*MID(A2,MIN(FIND({1,2},A2&"12")),4)),"")
D2:D8	D2	=TRIM(MID(SUBSTITUTE(MID($A2,SEARCH("Psa",$A2)+4,255)," ",REPT(" ",99)),99*2,99))
E2:E8	E2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A2,"."," "),SEARCH("#",$A2)+1,255)," ",REPT(" ",99)),99)),"")
F2:F8	F2	=TRIM(MID(SUBSTITUTE(MID($A2,SEARCH("Psa",$A2)+4,255)," ",REPT(" ",99)),99,99))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[HSAR]

DanteAmor

Thank you so much Sir.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.