Scientific Notation Issue Varying Decimal lengths

Kosvurd

New Member
Joined
Jun 26, 2012
Messages
7
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Kosvurd

New Member
Joined
Jun 26, 2012
Messages
7
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 26, 2012
Messages
7

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 26, 2012
Messages
7
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,492
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,990
Members
415,872
Latest member
ReignEternal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top