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

[loudnoiseman]

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

 

[queuesmef]

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]

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...

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

 

[queuesmef]

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