Excel字符串提取最大值问题：提取数字函数仅获取首个数值

Got it, let's tackle this problem head-on! You've got a string like ATCG=12.5,TTA=2.5,TGC=60.28 and want to pull out all the numeric values, then grab the largest one (which is 60.28 here). The trouble right now is your function only picks up the first number—so let's fix that with two approaches depending on your Excel version.

Solution for Excel 365 / Excel 2021 (Dynamic Array Functions) #

If you have access to Excel's newer dynamic array tools, this is straightforward and clean:

1. First, split the string into individual parts using TEXTSPLIT to break on both commas and equals signs:

   =TEXTSPLIT(A1,{",","="})
   

   This gives you an array like {"ATCG", 12.5, "TTA", 2.5, "TGC", 60.28}.

2. Next, filter out only the numeric values from that array with FILTER and ISNUMBER:

   =FILTER(TEXTSPLIT(A1,{",","="}), ISNUMBER(TEXTSPLIT(A1,{",","="})))
   

   Now you're left with just the numbers: {12.5, 2.5, 60.28}.

3. Finally, wrap it all in MAX to get the largest value:

   =MAX(FILTER(TEXTSPLIT(A1,{",","="}), ISNUMBER(TEXTSPLIT(A1,{",","="}))))
   

   Pop this formula in a cell (assuming your original string is in A1) and it'll spit out 60.28 right away. No extra steps needed—Excel handles the array automatically.

Solution for Older Excel Versions (No Dynamic Arrays) #

If you're using an older version without dynamic arrays, we'll use an array formula to get the same result. Here's how:

Paste this formula into a cell, then press Ctrl + Shift + Enter (not just Enter—this tells Excel it's an array formula):

=MAX(IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"=",","),",",REPT(" ",LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)))*LEN(A1)-LEN(A1)+1,LEN(A1))),0))

Let me break down what this does so you don't feel like you're just copying magic:

• SUBSTITUTE(A1,"=",","): Replaces all equals signs with commas, turning the string into ATCG,12.5,TTA,2.5,TGC,60.28.
• SUBSTITUTE(...,",",REPT(" ",LEN(A1))): Replaces each comma with a bunch of spaces (enough to cover the length of the original string).
• MID(...,ROW(INDIRECT("1:"&LEN(A1)))*LEN(A1)-LEN(A1)+1,LEN(A1)): Pulls out each "segment" of the spaced-out string one by one.
• TRIM(...): Removes all those extra spaces from each segment.
• --: Converts the trimmed text (if it's a number) into an actual numeric value.
• IFERROR(...,0): Turns any non-numeric segments (like the ATCG text) into 0 so they don't break the formula.
• MAX(...): Grabs the largest number from the resulting set of values.

Example Test #

If your original string is in cell A1:

Either formula will fill B1 with the correct maximum value.

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