Text Conversion to Numbers - Advanced

Q

QBERT

New Member
Joined
Oct 13, 2004
Messages
40
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
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
 

Attachments

  • 2023-08-09_12-37-28.png
    2023-08-09_12-37-28.png
    18.9 KB · Views: 6

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Do you want something like this?

Book1
DEFGHIJKLMNOP
12023-242024-252025-262026-272027-282023-242024-252025-262026-272027-28
270000007000000 M-NTC7000000 M-NTC7000000 M-NTC7000000 M-NTC70000007000000700000070000007000000
36250000 Full-NTC6250000 Full-NTC6250000 Full-NTC6250000 Full-NTCUFA6250000625000062500006250000
45825000 M-NTCUFA0005825000000
55250000 M-NTCUFA0005250000000
6
7
8
9
Sheet3
Cell Formulas
RangeFormula
K2:O5K2=IFERROR(--TEXTSPLIT(E2:I2&" "," "),"")
Dynamic array formulas.
 
Upvote 0
Problem Solved! I found a Character breaking space (160) after running CODE formula. Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Multiply 3 criteria with 2 conditions and sum the results for multiple columns.
Replies
5
Views
589
Doug_B
D
L
  • Question
Numbers as text
Replies
4
Views
471
Jeffrey Mahoney
Jeffrey Mahoney
B
  • Question
When is a number not a number? Match() does not recognize number.
Replies
7
Views
529
Tupe77
T
M
  • Solved
Ignore Row with Text Using VLOOKUP
Replies
7
Views
398
Alex Blakenburg
A
M
  • Question
Return pattern of text
Replies
2
Views
95
mib1019
M

Forum statistics

Threads
1,215,083
Messages
6,123,020
Members
449,092
Latest member
ikke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top