CLARIFICATION: I asked for a script because there are some other things I'm doing as well and I figured I would combine them all in a single script. But if a formula is the best way to do it, I can handle the other stuff as a separate part of the process. TY!
I'm trying to write a script to clean up a spreadsheet and need help understanding how best to approach it.
I have a spreadsheet that has node paths with nodes separated by slashes in a single cell, looks like this:
|ANTIQUES & COLLECTIBLES / Buying & Selling / Online Sales (see also BUSINESS & ECONOMICS / E-Commerce / Small Business)
|ARCHITECTURE / History / Modern (late 19th Century to 1945)
|BIBLES / International Children's Bible / General
|BUSINESS & ECONOMICS / Accounting / Standards (GAAP, IFRS, etc.)
I need to convert all uppercase values like
ARCHITECTURE to proper case. I tried converting the whole cell to proper case but this gives me issues with the
's showing as
19th Century as
Etc., and making the acronyms like
I found this answer to address
's but it seems like rather than addressing each of these use cases the best thing to do would be convert only the text before the first slash to proper case.
I could split the columns, convert to
proper and then concatenate but the parenthetical
"see also" statements complicate that as I also want to convert those parent node values to proper case, and the node paths aren't all the same length (so I can't just assume the uppercase values will always be in the same 2 columns necessarily)
I'm having trouble figuring out how to think this through to get the result I want. Appreciate any help understanding if there's a way to break this up or another way to think about it, to get what I need.