Molar Mass from Cactus in Excel 2013

Excel and Cactus

The Above Video describes how to use the NIH Cactus Chemical Resolver to make a column of molar masses from chemical names. Note, the molar mass fields need to be number fields with 4 digit spaces, and the fields with the URLs need to be text fields. You probably want to watch the video in full frame mode so you can read everything, and further information is available by clicking on the title.

____________________________________________________

There are several steps to the above video.

  1. Paste list of chemical names to a column.  This list has "sarin" as the first chemical.
  2. In the cell to the right of "sarin" paste the Cactus service url to acquire the information you seek, in this case the molar mass of Sarin
    http://cactus.nci.nih.gov/chemical/structure/sarin/mw
  3. Go to the cell below the cell in step 2 and use the Replace function in Excel to replace the word "sarin" in the above script with the chemical name in the first column of the chemical below "sarin".  This function has 4 attributes.
    1. Old Text:  Is the string you want to replace (the Cactus look-up URL in step 2)
    2. Start, num:  The number of characters from the start of the string where you want to start replacing text ("s" is the 46th character in).
    3. Num, char:  The number of characters you want to replace (Sarin has 5 characters, so we want to replace 5 characters)
    4. New_text:  We want to replace with the text of the cell next to this cell in the column of chemical names.  You can click the icon to the right of the text box for this input, or type in the cell's row and column.
       
  4. Grab the "black square" in the bottom right corner of the cell you made the replace function in, and drag down, repeating this for every cell in the column of names until they have all been embedded into the script.
  5. Go to the top of the next column (next to the cell with the script for Sarin) and use the Web Service Function
    1. Place the data in the cell with the script for the Sarin lookup into the parenthesis (do not paste the text, but the cell number).
    2. Click "enter" and the Molar Mass should be returned.
    3. Grab the black square in the bottom right corner and drag down, converting all you chemicals to their molar masses.
       

*Note*  The column of molar masses are connected to the two functions and if any of them change, the values will change.  So it is a good idea to copy the column of molar masses and paste it to another column as "(V) values" (the icon with "123")".  This is shown in the above video.

Rating: 
0
No votes yet
Join the conversation.

Comments 1

Robert Belford's picture
Robert Belford | Mon, 09/14/2015 - 19:34
Hi Everyone, Some of the questions concerning module 2 assignment dealt with getting CAS numbers and molar masses in bulk. I just discovered that Excel 2013 has built in Webservice functions, and so I made a video on these. Later this semester we will learn how to do this without using Excel's built in functions, and be able to access a lot more types of data using older versions of Excel. But the fact that Excel added this capacity shows that this type of action will become part of the workflow for tomorrow's scientist. Also, to give you an idea of how this website can grow. If you go to Module 2 <a href="http://olcc.ccce.divched.org/2015OLCCModule2P1">http://olcc.ccce.divched.org/2015OLCCModule2P1</a> and click section 2.6, it takes you down the page. Now if you click on the link of 2.6 (near the bottom of the module), it opens up the section where you can discuss section 2.6 (and should be able to upload files). But in the "Additional Material section, you now see a new link, which has the video I just created embedded in it, and if you click the video's title, you can get additional information and can discuss the video (which is what I am doing now). If any of you can figure how to do this with Excel 2010 you should chat with your instructor. As we progress through this course we are looking for interesting projects for students, and something like this might work, although you clearly need to discuss any projects you undertake with your instructor. Cheers, Bob Belford

Annotations