Google Sheets中实现指定字符组合替换为符号的技术问询

Core Approach #

To replace specific character pairs only when both exist, combine conditional checks with regex replacement. Here's how to implement it:

Formula Implementation #

Use LET to simplify repetitive code and add conditional logic:

=LET(
  original, JOIN("",ARRAYFORMULA(IF(INDEX(Tracker!C:AB,MATCH(A2,Tracker!A:A,0),Tracker!C:AB),Tracker!C1:AB1,))),
  IF(
    AND(REGEXMATCH(original, "A"), REGEXMATCH(original, "T")),
    REGEXREPLACE(original, "[AT]", "#"),
    original
  )
)

Breakdown of the Formula #

• LET(original, ...): Stores your original extracted letter string in a temporary variable to avoid repeating the full extraction formula.
• AND(REGEXMATCH(original, "A"), REGEXMATCH(original, "T")): Checks if both target characters (A and T) are present in the string.
• REGEXREPLACE(original, "[AT]", "#"): Replaces every occurrence of A or T with # only if both characters exist.
• Fallback: If either character is missing, returns the original unmodified string.

Handling Multiple Pairs #

If you need to handle additional character pairs (e.g., B and S replaced with @), extend the formula with nested IF statements inside LET:

=LET(
  original, JOIN("",ARRAYFORMULA(IF(INDEX(Tracker!C:AB,MATCH(A2,Tracker!A:A,0),Tracker!C:AB),Tracker!C1:AB1,))),
  step1, IF(AND(REGEXMATCH(original, "A"), REGEXMATCH(original, "T")), REGEXREPLACE(original, "[AT]", "#"), original),
  step2, IF(AND(REGEXMATCH(step1, "B"), REGEXMATCH(step1, "S")), REGEXREPLACE(step1, "[BS]", "@"), step1),
  step2
)

Notes #

• Replace "A", "T", and "#" with your actual target characters and replacement symbol as needed.
• The [AT] in the regex pattern matches any single occurrence of A or T; adjust this to match your pair characters.

内容的提问来源于stack exchange，提问作者Browndog