Excel技术咨询：如何提取两字符间字符串并分单元格存放

Hey there! Let's figure out how to extract all the text between pairs of single quotes (') in Excel, with each extracted string landing in its own separate cell. I’ve got two solid methods depending on which version of Excel you’re using—let’s dive in.

If you’re running the latest Excel with dynamic array functions, this is the fastest way—it’ll automatically spill all your quoted text into adjacent cells without needing separate formulas for each one.

Here’s the formula to use (replace A1 with your source text cell):

=FILTER(TEXTSPLIT(A1, "'"), MOD(SEQUENCE(COUNTA(TEXTSPLIT(A1, "'"))), 2) = 0)

How it works: #

• TEXTSPLIT(A1, "'") splits your original text into an array using single quotes as the delimiter. Every even-indexed item (starting from 0) in this array is the text inside the quotes we want.
• SEQUENCE(COUNTA(...)) creates a number sequence matching the length of the split array.
• MOD(..., 2) = 0 filters out all the odd-indexed items (the text outside quotes).
• FILTER() returns only the matching quoted text, which spills into cells to the right/down automatically.

If you don’t have dynamic array functions, you’ll need a unique formula for each cell where you want an extracted string. Here’s how to do it for the nth quoted string (replace N with 1, 2, 3, etc., depending on which pair you want to extract):

Formula for the nth quoted string: #

=TRIM(MID(SUBSTITUTE(A1, "'", REPT(" ", LEN(A1))), (2*N-1)*LEN(A1)+1, LEN(A1)))

Example breakdown: #

Suppose your source text in A1 is: 'First item' some random text 'Second value' more stuff 'Third result'

• To get the first quoted string, use N=1: =TRIM(MID(SUBSTITUTE(A1,"'",REPT(" ",LEN(A1))),1*LEN(A1)+1,LEN(A1))) → returns First item
• For the second, use N=2: =TRIM(MID(SUBSTITUTE(A1,"'",REPT(" ",LEN(A1))),3*LEN(A1)+1,LEN(A1))) → returns Second value
• For the third, N=3: =TRIM(MID(SUBSTITUTE(A1,"'",REPT(" ",LEN(A1))),5*LEN(A1)+1,LEN(A1))) → returns Third result

How this works: #

• SUBSTITUTE(A1, "'", REPT(" ", LEN(A1))) replaces every single quote with a block of spaces that’s the same length as your original text. This makes it easy to jump to the position of the nth quote pair.
• (2*N-1)*LEN(A1)+1 calculates the starting position of the text inside the nth quote pair.
• MID(..., LEN(A1)) extracts a chunk of text long enough to cover the quoted content.
• TRIM() cleans up any extra spaces left from the substitution.

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