Text Conversion to Numbers - Advanced

[QBERT]

Difficult Question.

I am doing contract work on NHL data from spotrac.com and need to extract only the numeric value of each contract by year as shown in my image. When I pasted the data as values into my Excel sheet it things that numbers are text and text is numbers and I cannot convert. I have tried a ton of methods and nothing so far is getting it done. What I am looking to do is get one contract value per year in Columns K to O for each Player.

I have tried VALUE, NUMBERVALUE, SUBSTITUTE, Text to COLUMNS, TEXT SPLIT, MULTIPLY, you name it. Nothing works. Please help. I would upload the sheet by the XL2BB add in is not working. Thanks,

Q

 

[Sufiyan97]

Do you want something like this?

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P
1		2023-24	2024-25	2025-26	2026-27	2027-28		2023-24	2024-25	2025-26	2026-27	2027-28
2		7000000	7000000 M-NTC	7000000 M-NTC	7000000 M-NTC	7000000 M-NTC		7000000	7000000	7000000	7000000	7000000
3		6250000 Full-NTC	6250000 Full-NTC	6250000 Full-NTC	6250000 Full-NTC	UFA		6250000	6250000	6250000	6250000
4		5825000 M-NTC	UFA	0	0	0		5825000		0	0	0
5		5250000 M-NTC	UFA	0	0	0		5250000		0	0	0
6
7
8
9
Sheet3

Cell Formulas
Range	Formula
K2:O5	K2	=IFERROR(--TEXTSPLIT(E2:I2&" "," "),"")
Dynamic array formulas.

 

[QBERT]

Problem Solved! I found a Character breaking space (160) after running CODE formula. Thank you!

 

[Sufiyan97]

Glad you sorted, thanks for follow-up.