Results 1 to 4 of 4

20140404, 13:07 #1
 Join Date
 Feb 2008
 Posts
 92
 Thanks
 10
 Thanked 0 Times in 0 Posts
Extracting text from the middle of the Cell
I have been the recipient of a lot of help over the years, and I thank everyone that has helped. I would like to give back with something.
I was given a list of chemicals and the Chemical Abstract Service Number (CAS No.) for each item. Unfortunately, the list was given as a simple number. Two examples would be
• Formaldehyde 50000
• Ammonia 7664417
The normal way of showing a CAS No. is with some dashes:
• Formaldehyde 50000
• Ammonia 7664417
I was looking for a way to extract this data into the typical format. It is complicated by the fact that the first set of numbers can be from two to six digits. A friend of mine came up with a really elegant way of doing this.
Note that the chemical name and the CAS No. are in two separate cells.
I put the CAS numbers into column E. Then I used the following formula, using the LEFT, RIGHT, and LEN functions:
=LEFT(E2,LEN(E2)3)&""&LEFT(RIGHT(E2,3),2)&""&RIGHT(E2,1)
As this is sort of a tutorial for those unfamiliar with ranges, I will detail what this does. It can be quite useful to extract information from inside the contents of a cell. It also shows how formulas can be nested.
First, the items between two quotation marks are inserted into the cell as text. Between each different part in the cell, an ampersand (&) must be used.
Then, starting at the end of the formula:
RIGHT(E2,1) means I start at the right of the cell E2, and take only the first character.
LEFT(RIGHT(E2,3),2) is really elegant. The innermost function in the nest is RIGHT(E2,3), which means the formula extracts the three characters from the right of E2. Then, the LEFT function comes into play, choosing only the first two digits found.
LEFT(E2,LEN(E2)3) takes into account the varying lengths of the strings. LEN(E2)3 starts by counting the number of characters in E2, and subtracts 3 from that number. That removes the final three characters described above. Then the LEFT function chooses the number of characters starting at the beginning of the string.[I have been here for years; I had to get things restarted]

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140404, 14:59 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,158
 Thanks
 14
 Thanked 315 Times in 309 Posts
If you want to keep it as a number, you could also just use a custom number format to display the number in the CAS format: 0000
Steve
PS a shorter alternative formula for generating the text string is:
=REPLACE(REPLACE(E2,LEN(E2),0,""),LEN(E2)2,0,"")
PPS. and an even shorter one is not to insert but use the custom format:
=TEXT(E2,"0000")Last edited by sdckapr; 20140404 at 15:56.

The Following User Says Thank You to sdckapr For This Useful Post:
JohnD1 (20140407)

20140404, 19:04 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,012
 Thanks
 37
 Thanked 176 Times in 163 Posts
Very Nice JohnD1. You could make a user defined function using your formula that would make it very easy to use:
Enter the formula in cell D1 =CAS(B1) then copy down
where A1 is the chemical name and B1 is the malformatted CAS number
Code:Public Function CAS(rng As Range) Application.Volatile CAS = Left(rng, Len(rng)  3) & "" & Left(Right(rng, 3), 2) & "" & Right(rng, 1) End Function
Last edited by Maudibe; 20140404 at 19:12.

20140407, 10:22 #4
 Join Date
 Feb 2008
 Posts
 92
 Thanks
 10
 Thanked 0 Times in 0 Posts