Excel’s auto formatting rules
It is important to understand that Microsoft Excel applies autoformatting rules when opening files. This often results in data loss due to the display limit.
If the number entered is more than 11 digits, it is automatically expressed in scientific notation.
If the number entered is more than 15 digits, all digits starting from the 16th digit are converted to 0 (causing data loss) and cannot be recovered. This can cause problems when these numbers are codes being used for analysis and linkage.
Excel can accommodate long strings of numbers, but you must format them to display as text to prevent data loss.
The GPDfPR reference file
The GPDfPR reference file contains clinical codes including SNOMED and Read codes which are listed in the dataset without any descriptions or additional context.
Do not open the reference file as a .txt or .csv file using Excel because this can lead to the final digits of SNOMED codes being converted to zeros by auto-rounding rules.
SNOMED codes can be up to 18 digits long and the autoformatting functions in Excel will treat the SNOMED codes as scientific or numeric values rather than text.
The code is then no longer correct and will not link correctly to the GPDfPR data tables.
Data loss opening directly in Excel
The first screenshot shows how Excel displays the SNOMED code and the data loss that occurs when you open the file directly in Excel.
The second screenshot displays the same field, but it has been imported into Excel without data loss.
This guide will work through the process to import the data into Excel so it loads correctly.
save the .csv file from the zipped file
Open the zipped file from your downloads and save the reference file file in an accessible location.
There are 3 ways to do this explained in the 3 options below.
Option 1 - Use the extract to button in the zip file application.
Option 2 - Use the right-click to copy files to clipboard and paste it in your chosen location (second screenshot).
Option 3 - Select, drag and drop the file to your chosen location.
Option 1 use the extract to
1.Select the file by clicking on it once.
2.Click on extract to which will load the options screen, seen in the first image below.
3.Select the location you want to save the file, in this case you can see downloads has been selected.
4.Click ok to complete the process.
Other ways to work with SNOMED codes
Formatting cells as text in Excel
You may want to paste SNOMED codes into Excel from another location.
In this instance select the relevant destinations cells (the screenshot below has selected all the destination cells) in Excel and change the format to text before pasting the data.
If you paste it and then change the format it won't work as the data will have been corrupted.
Pasting values as text into Excel
The data should then be pasted as values matching the destination formatting.
Last edited: 8 June 2022 3:41 pm