Converting Dates to CAS Registry Numbers in Excel

16 Mar

I am not sure how many of you encounter the situation of opening a file sent to you in CSV or TSV format into Excel and then seeing that the CASRN column contains some dates instead.

As an example see the set of records below in a file I am processing regarding endogenous metabolites

A portion of an Excel spreadsheet with CAS Numbers as dates

It is incredibly annoying, especially if you don’t check a large file for the presence of date format CASRNs, so I always check now simply by sorting. But what to do to fix it? There may be other ways but I have a simple solution that works and allows me to check and keep the file intact.

If you have a column of CAS Numbers then insert a new column adjacent to the CASRNs. Then insert a simple entry to convert the date to text format as follows: =TEXT(C2,”yyyy-mm-d”). It is obvious in the screenshot below.

Conversion formula: Date to CASRN format

in this example C2 is the cell containing the first CASRN. Then simply drag the corner of the first repaired cell to populate the column with repaired CASRNs.

Drag the corner of the cell to populate other CASRN repaired values

In general I then do a gap analysis of what we are missing in the CompTox Chemicals Dashboard ( by copying the list of CASRNs into the batch search page at and running a search with the CASRN identifier selected.

Batch Search download page – select CASRN as the input identifier, download in the format of choice – I recommend Excel instead of CSV considering this blog post!

By downloading the Excel spreadsheet, and taking advantage of our batch search checking the CAS Checksum the output can be very informative.

Searching the dashboard batch search for a list of CAS Numbers. 4429-04-3 is a valid CASRN but not in the database while three CASRN’s fail the Checksum.

It’s a simple solution…but hopefully useful.


About tony

Antony (Tony) J. Williams received his BSc in 1985 from the University of Liverpool (UK) and PhD in 1988 from the University of London (UK). His PhD research interests were in studying the effects of high pressure on molecular motions within lubricant related systems using Nuclear Magnetic Resonance. He moved to Ottawa, Canada to work for the National Research Council performing fundamental research on the electron paramagnetic resonance of radicals trapped in single crystals. Following his postdoctoral position he became the NMR Facility Manager for Ottawa University. Tony joined the Eastman Kodak Company in Rochester, New York as their NMR Technology Leader. He led the laboratory to develop quality control across multiple spectroscopy labs and helped establish walk-up laboratories providing NMR, LC-MS and other forms of spectroscopy to hundreds of chemists across multiple sites. This included the delivery of spectroscopic data to the desktop, automated processing and his initial interests in computer-assisted structure elucidation (CASE) systems. He also worked with a team to develop the worlds’ first web-based LIMS system, WIMS, capable of allowing chemical structure searching and spectral display. With his developing cheminformatic skills and passion for data management he left corporate America to join a small start-up company working out of Toronto, Canada. He joined ACD/Labs as their NMR Product Manager and various roles, including Chief Science Officer, during his 10 years with the company. His responsibilities included managing over 50 products at one time prior to developing a product management team, managing sales, marketing, technical support and technical services. ACD/Labs was one of Canada’s Fast 50 Tech Companies, and Forbes Fast 500 companies in 2001. His primary passions during his tenure with ACD/Labs was the continued adoption of web-based technologies and developing automated structure verification and elucidation platforms. While at ACD/Labs he suggested the possibility of developing a public resource for chemists attempting to integrate internet available chemical data. He finally pursued this vision with some close friends as a hobby project in the evenings and the result was the ChemSpider database ( Even while running out of a basement on hand built servers the website developed a large community following that eventually culminated in the acquisition of the website by the Royal Society of Chemistry (RSC) based in Cambridge, United Kingdom. Tony joined the organization, together with some of the other ChemSpider team, and became their Vice President of Strategic Development. At RSC he continued to develop cheminformatics tools, specifically ChemSpider, and was the technical lead for the chemistry aspects of the Open PHACTS project (, a project focused on the delivery of open data, open source and open systems to support the pharmaceutical sciences. He was also the technical lead for the UK National Chemical Database Service ( and the RSC lead for the PharmaSea project ( attempting to identify novel natural products from the ocean. He left RSC in 2015 to become a Computational Chemist in the National Center of Computational Toxicology at the Environmental Protection Agency where he is bringing his skills to bear working with a team on the delivery of a new software architecture for the management and delivery of data, algorithms and visualization tools. The “Chemistry Dashboard” was released on April 1st, no fooling, at, and provides access to over 700,000 chemicals, experimental and predicted properties and a developing link network to support the environmental sciences. Tony remains passionate about computer-assisted structure elucidation and verification approaches and continues to publish in this area. He is also passionate about teaching scientists to benefit from the developing array of social networking tools for scientists and is known as the ChemConnector on the networks. Over the years he has had adjunct roles at a number of institutions and presently enjoys working with scientists at both UNC Chapel Hill and NC State University. He is widely published with over 200 papers and book chapters and was the recipient of the Jim Gray Award for eScience in 2012. In 2016 he was awarded the North Carolina ACS Distinguished Speaker Award.
Leave a comment

Posted by on March 16, 2019 in CompTox dashboard, Data Quality


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.