I am developing an automation script for Excel Online using Office Scripts (TypeScript). I have a column of chemical formulas and mathematical expressions where I need to extract only the superscript characters. Standard cell values only return plain text, losing the formatting metadata. How can I access the rich text properties of a cell to detect which specific characters have the superscript attribute enabled, and is there a way to store these separately in another column?
3 answers
To read superscripts in Office Scripts, you cannot use the simple getValue() method because it strips all formatting. Instead, you must use the getRichText() method on the range object. This returns a GetRichText object which contains an array of text runs. You can iterate through these runs and check the verticalAlignment property of the font. If the property equals "Superscript", you can then extract that specific portion of the string. This is the modern way to handle granular formatting in Excel Online, replacing the old VBA Characters property which isn't available in the web version.
That is a great technical breakdown of the getRichText method! However, if a single cell contains multiple different superscripts—like a formula with both exponents and footnotes—how do you concatenate them back together without losing their relative positions? Is there a risk that the script might miss characters if the formatting was applied to a range of text rather than individual characters?
Using Office Scripts for this is much more reliable than trying to parse the characters manually. Just remember that getRichText only works on single cells at a time, so you'll need a loop if you're processing a large column.
I agree with Deborah. I implemented this for a Quality Management project where we had to extract version numbers from superscripted footnotes in audit logs. Using the getRichText().getRuns() approach was the only way to accurately pull that metadata out of Excel Online without manually re-typing thousands of entries.
Ryan, that's where the runs array is so helpful. Each "run" represents a continuous block of text with the same formatting. If you have "X² + Y³", the script sees "X" (Normal), "²" (Superscript), " + Y" (Normal), and "³" (Superscript). You can simply loop through the array and build a new string by only appending characters where run.getFont().getVerticalAlignment() === "Superscript". This ensures you maintain the logical order of the data during your software development process