0

I'm trying to write an Excel macro using VBA that will return only the first 5 numbers in a cell when the length of that cell exceeds 20. The field normally returns 15-digit alphanumeric results (which I need to leave alone) but in certain exceptions will return a 5-digit number with a multitude of zeroes following it (1234500000000000000000000...) which Excel converts into scientific notation (1.2345E+160). I am able to convert the cells to numbers instead of scientific notation and view the whole number.

I've tried to use code such as =IF(LEN(A1)>20,LEFT(A1,5),A1) and it just returns 1.2345E+160. Even though the whole number is displaying, Excel still thinks the cell length is 11 and won't display the first 5 digits.

I've also tried lines such as =IF(A1="E",LEFT(A1,6),A1) thinking it would detect the E, return 1.2345, and I could just remove the decimal points, but that didn't work either (it just returns the original 1.2345E+160).

I got the same results whether the cell was formatted as number or text. Is there a way around this?

Thank you for your time!

Ash
  • 9
  • 2
  • https://stackoverflow.com/questions/58288829/excels-len-function-returns-5-on-numbers-longer-than-20-characters – SJR Nov 12 '19 at 14:57

1 Answers1

2

You are trying to use string manipulation on a number. Instead use math: 

=A1/1E+160

If you do actually want to treat this thing as text, understand that the underlying value being stored is your 12345000000000000... and there is no decimal point in that thing. So you'll have to convert to text and add the decimal:

=LEFT(TEXT(A1,"0"), 1) & "." & MID(TEXT(A1,"0"), 2, 4)

But that's pretty ugly. I would just stick with math.

JNevill
  • 46,980
  • 4
  • 38
  • 63