Converting #'s away from scientific excel format

[Statz]

This is a two part question.

Original issue was I'm trying to use V look up to map UPC #'s to product ID's. I figured that out by using the substitute formula removing the "-".

The problem I have now is:

1. Removing leading ZERO.
2. Getting it to # that will work for V Look up but every time I convert it goes to that scientific number.

I attached what I was working on. Column E & F would be the new data from Column C & D but in a format that will work for V Look up.

Thank you

[Mvaldesi]

Maybe wrap your SUBSTITUE() in VALUE(), then highlight those columns, format cells as Number with "0" decimals and no 1000 separator?

=VALUE(SUBSTITUTE(A2,"-",""))

[Zer0Cool]

The issues your facing have to do with numbers vs text and what data type the functions your using return.

For example SUBSTITUTE in C & D are returning text values despite the cell being formatted as number (go ahead and toss =ISTEXT(C2) in another cell and see for yourself). Some function return text and others numbers, regardless of the values passed to them.

SUBSTITUTE is atext function, it returns text even if what it returns is a numeric digit or sequence of digits. Straight from the help for the function: "Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string."

Lookups generally work off whatever data types are used. So in a VLOOKUP for example the lookup value could be a numeric type and the range its looking in could contain text values, thus no match. For lookups you need things in like terms, that is to say all numeric or all text.

So you can either ensure the lookup value is a text entry to match the text values you have or change the results in your table_array to contain numeric data to match to a numeric lookup_value.

There are tricks to converting a digit or sequence of digits stored as text to number values. 1 possible solution is to multiple by 1. Any number * 1 = the number, and Excel is smart enough to know you cant multiple text so it effectively converts the number stored as text to numeric values (if the numeric sequence is a valid number).

Ex:

Please Login or Register  to view this content.

Another would be to use something like VALUE function provided.

[Statz]

Thank you!!