技术问询：求同时兼容Excel与Google Sheets的字符串拆分至指定单元格通用公式

Hey there, let’s cover exactly the formulas you need to split text into targeted cells—whether you’re sticking to Excel, or need something that works flawlessly in both Excel and Google Sheets.

Excel-Only Formulas #

1. Dynamic Spill Split (Excel 365/2021+) #

If you’re on a modern Excel version, TEXTSPLIT is the easiest way—it automatically splits your text into adjacent cells without dragging formulas around.

• Formula Syntax:

  =TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
  

• Quick Example:
  Cell A1 has Apple,Banana,Cherry? Split it by commas into B1, C1, D1 with:

  =TEXTSPLIT(A1, ",")
  

  Results will spill right automatically—no extra work needed.

2. Legacy Excel (Pre-365) – Manual Segment Extraction #

For older Excel versions that don’t support dynamic arrays, use combinations of LEFT, MID, RIGHT with FIND and LEN to target specific parts:

• First segment (before first comma):

  =LEFT(A1, FIND(",", A1) - 1)
  

• Middle segment (between first and second comma):

  =MID(A1, FIND(",", A1) + 1, FIND(",", A1, FIND(",", A1)+1) - FIND(",", A1) - 1)
  

• Last segment (after last comma):

  =RIGHT(A1, LEN(A1) - FIND("~", SUBSTITUTE(A1, ",", "~", LEN(A1)-LEN(SUBSTITUTE(A1, ",", "")))))
  

Cross-Platform Formulas (Excel + Google Sheets) #

These formulas work the same in both tools, so you can switch between platforms without rewriting anything.

1. Target a Specific Segment #

If you need a particular item from a delimited list (e.g., the 2nd item in a comma-separated string), use INDEX paired with SPLIT (works everywhere):

• Formula:

  =INDEX(SPLIT(A1, ","), 1, N)
  

  Replace N with the segment number you want (1 = first, 2 = second, etc.)
• Example:
  Get the 2nd item from Apple,Banana,Cherry in A1:

  =INDEX(SPLIT(A1, ","), 1, 2)
  

  Returns Banana in both Excel and Google Sheets.

2. Dynamic Spill to Adjacent Cells #

For auto-spilling results to nearby cells:

• Google Sheets: SPLIT does this by default:

  =SPLIT(A1, ",")
  

• Excel 365+: TEXTSPLIT works, but for a universal formula that falls back correctly:

  =IFERROR(TEXTSPLIT(A1, ","), SPLIT(A1, ","))
  

  This uses TEXTSPLIT in Excel if available, otherwise switches to SPLIT for Google Sheets.

3. Split by Fixed Character Length #

If you need to split text into equal-length chunks (e.g., every 3 characters):

• Formula:

  =MID(A1, (N-1)*CHUNK_LENGTH + 1, CHUNK_LENGTH)
  

  Replace CHUNK_LENGTH with your desired chunk size, N with the chunk number.
• Example:
  Split ABCDEFG into 3-character chunks:
  • 1st chunk: =MID(A1,1,3) → ABC
  • 2nd chunk: =MID(A1,4,3) → DEF
  • 3rd chunk: =MID(A1,7,3) → G

Pro Tip: Dealing with messy delimiters (like mixed spaces and commas)? Standardize first with SUBSTITUTE:

=SPLIT(SUBSTITUTE(A1, " ", ","), ",")

This turns any spaces into commas before splitting, making your data consistent.

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