How to take extremely large numbers Excel assumes is text and change back to numbers for math operations

loudnoiseman

Board Regular
Joined
Dec 31, 2004
Messages
215
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

I'm doing an analysis project where I've generated over a thousand tabs across a series of workbooks, all containing numbers that are measurements of factors resulting from a bunch of formulas that analyze a large dataset of raw data. And I'm doing math operations on almost all factors without issue.

Except one factor is express in a 20-digit number. I used a macro to copy all this data to the tabs and I've found that it's storing this one number as text and I can't do any math operations on them with other formulas.

So my question is, is there a solution within a formula to recognize numbers within text formatted cells to allow simple subtraction?

I wish I asked this question one thousand tabs ago, but there it is. I appreciate the help!

Loudnoiseman
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

queuesmef

Board Regular
Joined
Dec 19, 2016
Messages
76
it would be easier if you submitted an example of what you mean.
But you can try converting the text to numbers it the 'Problem' column. (Data tab/ text to Columns0
also try formula to convert text to number - =Value( ).
 

loudnoiseman

Board Regular
Joined
Dec 31, 2004
Messages
215
Office Version
  1. 365
Platform
  1. Windows
Actually subtraction does work - sorry - but RANK gives the error I was thinking of; disabled formula in B, active formula in C. It can't seem to rank a range of numbers it thinks is text...

100000000000000101001RANK.EQ(A1,A:A,1)#N/A
100000000000001010000RANK.EQ(A2,A:A,1)#N/A
100000000000001011110RANK.EQ(A3,A:A,1)#N/A
100000000000010000010RANK.EQ(A4,A:A,1)#N/A
100000000000100010011RANK.EQ(A5,A:A,1)#N/A
100000000000110010010RANK.EQ(A6,A:A,1)#N/A
100000000000111100100RANK.EQ(A7,A:A,1)#N/A
100000000001000100111RANK.EQ(A8,A:A,1)#N/A
100000000001011101000RANK.EQ(A9,A:A,1)#N/A
100000000001011111110RANK.EQ(A10,A:A,1)#N/A
100000000001100010010RANK.EQ(A11,A:A,1)#N/A
100000000001100110101RANK.EQ(A12,A:A,1)#N/A
100000000010000000010RANK.EQ(A13,A:A,1)#N/A
100000000010010011101RANK.EQ(A14,A:A,1)#N/A
100000000010010011111RANK.EQ(A15,A:A,1)#N/A
100000000010010011111RANK.EQ(A16,A:A,1)#N/A
100000000010010100001RANK.EQ(A17,A:A,1)#N/A
100000000010100101011RANK.EQ(A18,A:A,1)#N/A
100000000010110111101RANK.EQ(A19,A:A,1)#N/A
100000000100000100100RANK.EQ(A20,A:A,1)#N/A
 

queuesmef

Board Regular
Joined
Dec 19, 2016
Messages
76
I would create a HELPER Column where you would Range Value the Text into Numbers, then base your formulas off of the HELPER Column. Use either Data/ColumnToText OR the =VALUE formula
 
Solution

Forum statistics

Threads
1,176,086
Messages
5,901,305
Members
434,886
Latest member
qazibelal

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
Top