Problem with numbers saved as text

**livifivil** · 06-28-2013, 03:03 PM

I have a problem because I need to gather statistics based on a unique value. This unique value shows up across 5 sheets on the same workbook. The issue I am having is that I am using a VLOOKUP formula to find things and it isn't matching to anything. I feel that the reason is because all of the 16 digit unique values are saved as text because, if not, they look like this, 1.00095E+15. Is there a way to change all of these values to numbers but not change them to exponents?

**xladept** · 06-28-2013, 03:08 PM

Try multiplying each by one

**MrShorty** · 06-28-2013, 03:14 PM

How important is the 16th digit? If you multiply by 1 (or otherwise convert the text string to a number), you will lose the 16th digit, as Excel (and every other program that uses double precision to store numbers) is limited to 15 digits of precision. Before recommending an approach, you should decide if you want to convert to numbers and lose the 16th digit, or leave them as text so all 16 digits are retained.

The conversion to exponent is purely a formatting thing (because most default cells are not wide enough to show 15 digit numbers, and exponential notation is Excel's way of handling numbers that don't fit). If the exponential notation bothers you, make sure the column is wide enough and format as "0".

**livifivil** · 06-28-2013, 03:16 PM

It's not returning the correct value. Example below.

Old New
1000912419593011 1000912419593010
1000903461943014 1000903461943010
1000909964083018 1000909964083010
1000908368853018 1000908368853010
1000907976543011 1000907976543010

**livifivil** · 06-28-2013, 03:18 PM

It's important because that's part of what makes the values unique. I need it to be all 16 digits.

**livifivil** · 06-28-2013, 03:23 PM

Actually, this is all just a work around for what I am ultimately trying to accomplish with this spreadsheet. If I made a sample workbook with an example, would that be a little more helpful?

**MrShorty** · 06-28-2013, 03:59 PM

A sample would probably help.

Since you need all 16 digits, you will need to keep the values as text strings. You will also need to make sure your lookup value is a text string and not a number (this might be why the original lookup was failing). This means preceding a number hand entered into a cell with an apostrophe ('1234567890123456) so Excel will recognize it as text or otherwise making sure the number is always seen as text and never a number.

**livifivil** · 06-28-2013, 04:22 PM

Attached is a sample grid. Basically my issue is that I do not know the formula that would create the stats I am looking for. I put the formulas I am looking for on the stats page. All of these formulas will also have to include a date filter. I have the formula for that as I have used it a lot in the past. It is as follows.

Sheet1!$E:$E,">="&Sheet2!$B$4,Sheet1!$E:$E,"<="&Sheet2!$B$5

B4 = start date
B5 = end date

What I was trying to accomplish was a work around where I could use VLOOKUP to link a name and a result to each record on the middle 3 sheets using the "VIP" sheet as my table. This way I could just use COUNTIFS to return the stats I needed. Unfortunately, the formula was not matching anything and I thought it was because the numbers were saved as text. Anyway, any help with this would be greatly appreciated. Either a formula that takes care of all this or a work around I can use until I find something better. Please keep in mind that every sheet on this grid will have hundreds of records. I just deleted all but one on each sheet so it wasn't so big. THANKS!!