Scientific Notation Issue Varying Decimal lengths

[Kosvurd]

Hi Team.
You have never let me down as I can always rely on this team for answers. I have thousands of records where the number has varying decimal lengths ranging from 1 to 4 decimal places (e.g. 3000123.1 to 3000546.4444). The decimals are versions so ".1" is different than ".1000". I need to keep the versions intact. Excel is converting to scientific notation and simply changing the data to a number format with 4 decimals places will not work as it changes the data. Does anyone have any ideas how I can convert the scientific notation without jeopardizing the integrity of the data? Thx team.

 

[Joe4]

With numbers, Excel can only track 15 significant digits. If your entries have more than that, and if they are currently showing it in scientific notation, you have already lost all numbers after the 15th digit.

The secret is to enter these as text, and not numbers. Then you will not lost any digits.
Also note that if you are currently using numbers, you will lose any trailing 0s in your decimals (i.e. if you enter 123.10, Excel will change it to 123.1). So this is another reason to enter them as text.

You can convert current entries from numbers to text by using "Text to Columns", and choosing the "Text" option on step 3.

 

[Kosvurd]

Thanks Joe4. I have tried to do that unsuccessfully. Perhaps you can tell me what I am doing wrong. I have opened new spreadsheet, formatted the entire sheet as Text, copied the numbers with decimals and paste special values and it never works. What am I doing wrong?

 

[Joe4]

I have opened new spreadsheet, formatted the entire sheet as Text, copied the numbers with decimals and paste special values and it never works. What am I doing wrong?

Copying from where?

Note that when you copy/paste data, it will copy the formatting too (and overwrite any formatting that you set to the new range before hand).
You either need to address the data as it is being entered/imported, or fix it after the fact.

To fix it after the fact, you would use the "Text to Columns" method I described in my previous post. However note, that any fix "after the fact" will already be subject to losing all digits after 15, and all trailing zeroes in decimals (the fix can only run against what is currently showing - it has no memory of what it was before or where it came from).

For Text to Columns, do the following:
1. Select the column in question
2. Go to "Text to Columns", found on the data menu
3. Click "Next" a few times until you get to step 3
4. Select the "Text" radio button option
5. Click Finish

For any new manual entries, if you pre-format those cells to be "Text" before data entry, it will retain every character you enter, as-is, without changing it.

 

[Kosvurd]

Hi Joe4. I think I figured out another solution. A little more time consuming but it works. So, I did a text to column like you shared - ex. 1234.10. So, my data is now Column A1 (1234), Column B1 (.) and Column C1 (10). Then in Column D is went to Formula, Text, CONCAT like this...=CONCAT(A1,B1,C1). The output preserved trailing zero. Output = 1234.10. I appreciate you helping me think through this.

 

[Joe4]

Hi Joe4. I think I figured out another solution. A little more time consuming but it works. So, I did a text to column like you shared - ex. 1234.10. So, my data is now Column A1 (1234), Column B1 (.) and Column C1 (10). Then in Column D is went to Formula, Text, CONCAT like this...=CONCAT(A1,B1,C1). The output preserved trailing zero. Output = 1234.10. I appreciate you helping me think through this.

Why not just do the Text to Columns, outputting to Text, like I described step-by-step in my previous reply?
It is shorter and has no need of extra columns or formulas!
And I see no additional benefit that the longer way has to the shorter one.

 

[Kosvurd]

Hi Joe4. I misunderstood so I went back and tried the Text to Column again...key being the "Text" option on step 3. That is totally easier. Yes, thank you.

 

[Joe4]

You are welcome.