Excel molecular formula subscript help

[megalomania]

I am redoing the molecular format of the energetic materials on my website and I am running into an annoying problem. I have all of the molecular formulas properly subscripted in word, but subscripts do not copy and paste into Dreamweaver. In Dreamweaver the sub/sup script code snippet is buried deep, so manually changing every occurrence of a number to have a subscript tag wrapped around it is quite tedious.

What I have are a large number of formulas that have no subscript at all. I want to paste these into my excel list which I use to organize and sort data. I can’t figure out a way to write a (excel)formula that will take a number in the format of CxHyNzOw and add the appropriate tags around the number. I also tried using regular expressions to make the replacements in Dreamweaver. I can FIND the numbers, but I can’t seem to add content both before and after the number while keeping that same number.

Not all formulas will have N or O, some have additional molecules, and the numbers can be 1 or 2 digits long, and in a few cases 3.
How can I take a formula such as C6H2(NO2)3ONH4 and using an excel formula display this in the format of C₆H₂(NO₂)₃ONH₄? Obviously the example formula does not follow the usual CxHyAzBwCvDu format of an empirical formula; not all formulas I have follow the format, so no formula can anticipate this format. I want a formula that will take digit X and make it _X.

I hope I explained that clearly. I don’t want a “find and replace” I want a “find and add before and after” type formula that only works on digits in varying lengths and locations in a text string.

[megalomania]

For fucks sake, I just wasted more time figuring this out than it would have taken to do manually, but now I have a few more mad skillz with excel...

I saved myself a good deal of time by using that "extract data and text from multiple files" program I hunted down 1.5 years ago. I was able to generate a list of every molecular formula from my master word document and drop that in my excel file. I spent over 45 hours in 4 days completely redoing my word and excel sheets to get them all alphabetized and organized... a good thing now because the order of extracted formulas now matches the alphabetized list of molecular names exactly.

I ended up using dreamweaver's find and replace function with regular expressions to get the job done. I used these parameters:
find: (\D)(\d)
replace: $1_$2

This expression finds a letter (molecular symbol), using the \D wildcard, followed by a number, using the \d wildcard, but not any numbers that would precede a letter, such as 5H2O. The wildcards are in parentheses in order to assign them to $1 and $2; the first parentheses finds the letter, which is replaced as $1, and the second parentheses finds the number, which is replaced as $2.

Alas, this replace operation only works on single digits, so any double digit number would only have the first digit subscripted. I ran a second find and replace operation to fix that:
find: _(\d)(\d)
replace: _$1$2
This finds any occurrence of a subscripted number followed immediately by another number and moves the closing subscript tag to after the second number.

I had to manually do the tags for any subscripts using letters (indeterminate formulas) and salts that include a prefix number followed by a formula that already had some part of the formula before it (like the pentahydrate of copper sulfate pentahydrate, CuSO4 5H2O). Doing these special cases was easy with everything in an excel list.

I would still prefer an excel formula because the code will automatically update if I make changes, or add additional formulas, but this way works.

[FUTI]

Mega I think I have seen some book Excel for chemists some time ago. I haven't got time to read it but maybe the answer to your problem is there.

[Bugger]

Excel For Chemists
Almost a year ago, I downloaded "Excel For Chemists" by E.J. Billo (Wiley - 2nd edition - 2001), which runs to a 60 Mb PDF. Here is a current link for it, which has been going since February 2007 with over 400 downloads:
http://mihd.net/e71glc

There were also rapidshare.de links for it, but these have expired.

See also:
http://web.uvic.ca/~berryde/chem222/Excel%20tutorial/Excel%20Tutorial.pdf
http://depts.washington.edu/chem/courses/labs/documents/Chem142_ExcelTutorialPart1.pdf
http://depts.washington.edu/chem/courses/labs/documents/Chem142_ExcelTutorialPart2.pdf

[megalomania]

I already have the book, and I read through it before I asked the question. It did not have any answer that I could find, despite the book being the most logical choice for my problem. The book exclusively focuses on formulas for statistics, graphs, data analysis, and more esoteric sorting of data. It is rather short on helpful hints for formatting.

[Bugger]

The Excel Bible (PDF):
http://rs7tg.rapidshare.com/files/62044489/073562321X.rar
73.24 Mb