Google Sheets中如何在含大量引号的文本中插入多单元格值？

问题说明 #

需要将Google Sheets中D6:D15、E6:E15的计算值，替换到目标配置文本里所有X=和Y=后的数值。原文本包含大量引号，直接拼接公式时容易出现语法错误。

解决方案 #

方法1：分段拼接+TEXTJOIN（易维护） #

把配置文本拆分为固定前缀、单个兴趣点模板、固定后缀三部分，批量生成后再拼接：

1. 生成单个兴趣点内容

   • 在F6单元格输入前8个兴趣点的通用模板：

     ="(PointOfInterestID="&ROW(A1)-1&",PointOfInterestName=""Power Stone "&ROW(A1)&""",UnlockFeatNames=(""Dance"&ROW(A1)&"""),WorldMapPosition=(X="&D6&",Y="&E6&"),CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Item_InfinityGem_Icon"&ROW(A1)&".Item_InfinityGem_Icon"&ROW(A1)&"',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Item_InfinityGem_Icon10.Item_InfinityGem_Icon10')"
     

     下拉到F13即可生成前8个兴趣点的内容。
   • 在F14单元格单独输入第9个兴趣点：

     ="(PointOfInterestID=8,PointOfInterestName=""Power Stone 9 - Ghost Ship Route"",UnlockFeatNames=(""Dance9""),WorldMapPosition=(X="&D14&",Y="&E14&"),CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_GhostShip_Icon_Complete.HUD_GhostShip_Icon_Complete',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_GhostShip_Icon.HUD_GhostShip_Icon')"
     

   • 在F15单元格单独输入第10个兴趣点：

     ="(PointOfInterestID=9,PointOfInterestName=""Bring all 9 Power Stones to Center Maw"",WorldMapPosition=(X="&D15&",Y="&E15&"),CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_BossLocation_Icon_Complete.HUD_BossLocation_Icon_Complete',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_BossLocation_Icon.HUD_BossLocation_Icon')"
     

2. 拼接所有兴趣点
   在G6单元格用TEXTJOIN合并F6:F15的内容：

   =TEXTJOIN(",", TRUE, F6:F15)
   

3. 生成完整配置文本
   在任意空白单元格输入拼接公式：

   ="(QuestEntries=((QuestID=0,CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Icon_PowerStonesQuest_Complete.Icon_PowerStonesQuest_Complete',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Icon_PowerStonesQuest_Uncomplete.Icon_PowerStonesQuest_Uncomplete',QuestName=""Voyage of Power"",QuestDescription=""Journey across the ATLAS to hunt for the Power Stones, and then bring them to the Center Maw!"",UnlockFeatNames=(""Dance10""),QuestPointsOfInterest=("&G6&"))),"
   

方法2：修正ArrayFormula写法 #

原公式错误使用了反斜杠转义引号（Google Sheets仅支持双引号转义双引号），修正后用数组批量生成：

=ArrayFormula(
  "(QuestEntries=((QuestID=0,CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Icon_PowerStonesQuest_Complete.Icon_PowerStonesQuest_Complete',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Icon_PowerStonesQuest_Uncomplete.Icon_PowerStonesQuest_Uncomplete',QuestName=""Voyage of Power"",QuestDescription=""Journey across the ATLAS to hunt for the Power Stones, and then bring them to the Center Maw!"",UnlockFeatNames=(""Dance10""),QuestPointsOfInterest=("&
  TEXTJOIN(",", TRUE,
    IF(ROW(D6:D15)-5<=8,
      "(PointOfInterestID="&ROW(D6:D15)-6&",PointOfInterestName=""Power Stone "&ROW(D6:D15)-5&""",UnlockFeatNames=(""Dance"&ROW(D6:D15)-5&"""),WorldMapPosition=(X="&D6:D15&",Y="&E6:E15&"),CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Item_InfinityGem_Icon"&ROW(D6:D15)-5&".Item_InfinityGem_Icon"&ROW(D6:D15)-5&"',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/Item_InfinityGem_Icon10.Item_InfinityGem_Icon10')",
      IF(ROW(D6:D15)-5=9,
        "(PointOfInterestID=8,PointOfInterestName=""Power Stone 9 - Ghost Ship Route"",UnlockFeatNames=(""Dance9""),WorldMapPosition=(X="&D14&",Y="&E14&"),CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_GhostShip_Icon_Complete.HUD_GhostShip_Icon_Complete',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_GhostShip_Icon.HUD_GhostShip_Icon')",
        "(PointOfInterestID=9,PointOfInterestName=""Bring all 9 Power Stones to Center Maw"",WorldMapPosition=(X="&D15&",Y="&E15&"),CompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_BossLocation_Icon_Complete.HUD_BossLocation_Icon_Complete',UncompletedIcon=Texture2D'/Game/Atlas/UI/Icons/HUD_BossLocation_Icon.HUD_BossLocation_Icon')"
      )
    )
  )&
  "))),"
)

关键注意事项 #

• Google Sheets中字符串内的双引号必须用两个连续双引号转义，不能用反斜杠
• 第9、10个兴趣点的内容与前8个不同，需单独判断处理，避免批量公式出错
• TEXTJOIN会自动忽略空单元格，确保拼接内容连贯

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