Excel - Long Number Help

[kujeremy]

First - I'm sure that there's already a solution for my problem out there somewhere, and I have looked and looked but have so far been unsuccessful in achieving the results I am trying to get, so sorry that this is probably an often repeated question!

I have some long numbers that Excel is destroying and I can't figure out a workaround. I have 70,000+ records on a spreadsheet which has a column of numbers that are 23 characters long.
These numbers are all similar to the below (once I convert them to numbers from the 1.00016E+22 that excel puts them in initially)

10252866400926800000000
57100525138106800000000

What I need to be able to do is do is get rid of the ending 800000000 from every record. I've tried splitting column, find and replace, trimming from the right, etc and none work. I don't know what else to do so am hoping that someone on this message board has an idea.

Thanks

 

[Joe4]

Welcome to the Board!

The key is to enter them as Text and not Numbers. If you enter them as numbers, they will be converted to Scientific Notation.
Since I doubt that you are doing any math functions on them, there shouldn't be a problem with entering them as text.
You can then use LEFT, RIGHT, MID, SUBSTITUTE, and other text functions to carve out whatever you want.

 

[kujeremy]

So I am not actually entering them - these are all passed to me from a third party system in a csv file. When I get them in that file they are all formatted like: 1.00016E+22 If I highlight the column and change them to text they remain like 1.00016E+22

 

[Joe4]

When I get them in that file they are all formatted like: 1.00016E+22

Actually, they probably aren't. The issue is when you open a CSV file directly into Excel, Excel automatically does data conversions/manipulations. Anything that looks like a number, it will treat like one.
If you view the CSV in a Text Editor (like NotePad or WordPad), you will see the CSV file in its native state. If you look at it there, do those numbers look correct (I suspect they do)?

So, the key is to open the CSV file in Excel in a manner which allows you to designate the format of each file coming in (instead of having Excel do it for you automatically).
Do the following:
- Go into Excel, and open a new blank document
- From the Data menu tab, click on the "From Text" button on the "Get External Data" ribbon
- Browse to and select your CSV file
- This should invoke the Import Wizard
- In Step 1, select the "Delimited" button and click "Next"
- In Step 2, check the "Comma" button and click "Next"
- In Step 3, highlight the number field in the Data preview pane, and then select the "Text" button
- Click "Finish"

That should bring in those values as text, which will keep them as-is and not convert them to scientific notation.

 

[kujeremy]

Thank you so much! I'd actually imported previously - but left the import option as General and not Text. This looks to be resolved. Thanks so much!

 

[Joe4]

You are welcome!