Excel not handling large number correctly

[esukkar]

Hi all,

I have a VBA script that copies a large ID number for product. The ID is split into the, the ID, and the prefix.

an example is: 54930DEFDEFW4545434I20230502045000000000000002671786

In this case, the Prefix is the first 20 characters
54930DEFDEFW4545434I

and the ID is
20230502045000000000000002671786

I have VBA split the string into two using the code:



x = 54930DEFDEFW4545434I20230502045000000000000002671786
prefix = Right(x, Len(x) - 20)
id = Left(x, 20)

the problem is that excel doesn't seem to be able to handle numbers that long - that when prefix is pasted to a cell, instead of getting
20230502045000000000000002671786
I get



2.02305E+31




if I change the format to number with zero decimal places, I get


20230502045000000000000000000000



its as if Excel cannot handle long number types

I would change the type to long, however, the ID isn't always numeric, it can also contrain alphabetic characters. so that


20230502045000000000GTGHT00000000 is also a valid ID

Is anyone able to help with this?

thanks in advance.

[arnelgp]

in real world, you only need the most 13 digit/sku/pku/ident for your product.
large number is an exaggeration.

[Paul_Hossler]

Try



Dim prefix As String


prefix = "'" & Right(x, Len(x) - 20) ' DoubleQuote SingleQuote DoubleQuote

[esukkar]

Try



Dim prefix As String


prefix = "'" & Right(x, Len(x) - 20) ' DoubleQuote SingleQuote DoubleQuote





thank you - that worked