CONCATENATE FUNCTION PROBLEM

HEY,

I HAVE EXCEL 2007, THE PROBLEM IS WHIT CONCATENATE FUNCTION.

WHEN I'M DOING =CONCATENATE(999711100100,300,10196) I GETTING THE NUMBER
99971110010030010196

so far so good, but when i'm doing on the number f2 and then f9 and then enter (convert it to a number) ,i'm getting adifferent number (99971110010030000000).

i must convert it to anumber becaue i want to do on this number a vlookup function.

can you help me please?

thanks.
efi

AttachmentSize
vlookup_large_string.xlsx9.37 KB

Why do you need to convert

Why do you need to convert the string to a numerical value? Vlookup and sort will work on strings I cannot see a situation in which you would need to perform numerical calculations on this value

i try it' it dosent work

hey,
thank you for your patience

i can't' when i have 13000 serial numbers it's don't recognized all the serial numbers when i concatenate all the numbers.

let's say thet i don't want to concatenate
let's say that i'm onley want to type the number 99971110010030010196 why i don't get the same number thet i typed?

try to type the number
99971110010030010196.

you will see that you don't get the same number.

it's not normal.

thanks,
efi

Concatenate big number

Hi,

Excel can not manage well number this big.
Concatenate returns a string, vlookup can work with string.

concatenate problem

hey,

ok, but i need it to do report in my work.

it complex from serial number, the number of the vendor
and the area code of one of the group of the factory.

are you sure that i don't have any option ?

thanks,
efi

Nick's picture

Efi - I have attached an

Efi - I have attached an example to your post that shows you how it can work..
- if pasting large numbers into Excel, you need to make sure that the column is formatted as text first otherwise Excel will convert.

- if you F2 and enter with the cell not formatted as text, Excel will convert it, and you will lose the trailing zeros

nick - thnak you helped me alot

thanks :)