-1

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:

Node Paths
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 'S, 19th Century as 19Th Century, etc. as Etc., and making the acronyms like GAAP into Gaap.

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.

3
  • The answer you linked to is for a function/formula not a Google Apps Script. Please review 'How to ask a good question' then edit your question to reflect what you specifically want to do and whether a formula-based solution is acceptable. Show what you have tried and any additional use cases such as the "see also" one mentioned.
    – Blindspots
    Jun 28, 2023 at 21:55
  • Welcome to Web Applications. This is not a place to "ask for a script"; instead, you should ask for help using Google Sheets and Google Apps Script as web applications. You should narrow down the problem and present it as a practical, detailed question about using a web application. As part of the problem description, you should describe the relevant parts of your spreadsheets, including sample data, the expected result, show what you have tried and related questions that you have found explaining why they don't meet your needs. This is b/c this platform is not well suited to handle questions... Jun 30, 2023 at 0:41
  • ... that requires a lot of back and forth. It's intended to be a library of knowledge through questions and answers, not a log of service tickets. Jun 30, 2023 at 0:41

2 Answers 2

1

This approach uses REGEXTRACT and SUBSTITUTE and handles both use cases

=BYROW(FILTER(A:A,LEN(A:A)), LAMBDA(r,
  LET(rxA,"^", rxB,"see also ",
  rxC,"([[:upper:]]{3,}(?:\s&\s[[:upper:]]{3,})?)",
  sub, LAMBDA(a,b, IFERROR(REGEXEXTRACT(r, a & b))),
  subA,sub(rxA,rxC), subB,sub(rxB,rxC),
  SUBSTITUTE(SUBSTITUTE(r,subA,PROPER(subA)),subB,PROPER(subB)))))

1
  • 1
    this works great thank you! appreciate it.
    – bcknblf
    Jun 29, 2023 at 22:10
1

The question specifies , but this is probably easier to solve with a plain vanilla spreadsheet formula, like this:

=let( 
  paths, A2:A, 
  mark, "(see also ",
  first_, lambda(t, ifna(proper(regexextract(t, "(.+?)/")), t)), 
  rest_, lambda(t, ifna(regexextract(t, "(/.+?)(?:\" & mark & "|$)"))), 
  par_, lambda(t, ifna(regexextract(t, "\" & mark & "(.+?\))"))), 
  proper_, lambda(t, 
    ifs( 
      not(istext(t)), to_text(t), 
      not(regexmatch(t, "/")), proper(t), 
      true, let( 
        par, par_(t), 
        first_(t) & rest_(t) & 
        if( 
          len(par), 
          mark & first_(par) & rest_(par), 
          "" 
        ) 
      ) 
    ) 
  ), 
  map(paths, proper_) 
)

The formula handles edge cases such as (see also GAAP, IFRS, etc.):

Node Paths Proper case
ANTIQUES & COLLECTIBLES / Buying & Selling / Online Sales (see also BUSINESS & ECONOMICS / E-Commerce / Small Business) Antiques & Collectibles / Buying & Selling / Online Sales (see also Business & Economics / E-Commerce / Small Business)
ARCHITECTURE / History / Modern (late 19th Century to 1945) Architecture / History / Modern (late 19th Century to 1945)
BIBLES / International Children's Bible / General Bibles / International Children's Bible / General
BUSINESS & ECONOMICS / Accounting / Standards (GAAP, IFRS, etc.) Business & Economics / Accounting / Standards (GAAP, IFRS, etc.)
BUSINESS & ECONOMICS / Accounting / Standards (see also GAAP, IFRS, etc.) Business & Economics / Accounting / Standards (see also GAAP, IFRS, etc.)
BUSINESS & ECONOMICS Business & Economics
2022-01 2022-01
6/29/2023 10:20:38 6/29/2023 10:20:38
1
  • Not sure why someone downvoted this answer as it addresses the question.
    – Blindspots
    Jul 1, 2023 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.