从英数字元字串中撷取数字 http://office.microsoft.com/zh-tw/excel/HA011549011028.aspx?pid=CL100570551028 套用至: Microsoft Office Excel 2003 本文作者是 Microsoft 最有价值的专业人员 (MVP，Most Valuable Professional) Ashish Mathur。如需详细资讯，请造访 Microsoft MVP 网站 (英文)。 我们将在本文中针对一道能将数字从字串中撷取出来的公式进行说明。此公式适用於下列 字串： * 字母字元和数字字元个别丛集的字串，例如 abc123 或 678sfr。 * 字母字元和数字字元并非个别丛集的字串，例如 abc15tni。 问题 如何将某个英数数元字串中的数字部份撷取出来。例如：如果储存格 A1 包含字串「 abc123」，则将值 123 传回储存格 B1 中。 解决方案 此解决方案的潜在逻辑是找出该英数字元字串中的第一个数字，并仅传回该数字及其後的 所有数字。 演算法 此解决方案涉及建立能够达成下列任务的公式： 1. 将英数字元字串打散为个别的字元。 2. 判别打散的字串中是否有数字的存在。 3. 判别数字在英数字元字串中的位置。 4. 计算英数字元字串中的数字总数。 我们将会分别考虑上述任务，再整合出能够产生最终结果的公式。 将英数字元字串打散为个别的字元 我们将在此使用 MID 函数。MID 会自文字字串中您所指定的位置开始，传回特定数目的 字元 (依据您指定的字元数目)。该函数的语法为： MID(text,start_num,num_chars) * text 包含您想要撷取之字元的文字字串。 * Start_num 您想要在 text 中撷取之第一个字元的位置。text 中第一个字元的 start_num 为 1，依此类推。 * Num_chars 指定您想要让 MID 从 text 中传回多少字元。 本范例所使用的公式为： =MID(A1,ROW($1:$9),1) 此公式会将英数字元字串打散，并将字元— 以虚拟方式— 置於工作表的不同列中。以 abc123 这个英数字元字串为例，全部 6 个字元均会被打散。 附注 您可以适当地增加 9 这个数字，以符合较长的字串。在本范例中，最大的字串长 度为 9。 值得注意的是，将字串打散时，「1」、「2」和「3」将被视为文字— 而非数字。如果要 将储存为文字的数字转换为数字，只要将公式乘以 1 即可。例如： =1*MID(A1,ROW($1:$9),1) 判别打散的字串中是否有数字的存在 这里我们将会使用 ISNUMBER 函数来判别英数字元字串中是否有数字的存在。此公式目前 修正为： =ISNUMBER(1*MID(A1,ROW($1:$9),1)) 如果字串中有数字的存在，结果便会是 TRUE，否则结果便是 FALSE。 判别数字在英数字元字串中的位置 现在我们将在已打散的字串所传回的结果中寻找 TRUE 值 (请见上一段的说明)，以判别 数字的位置。这里我们将会使用 MATCH 函数。此公式目前修正为： =MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0) 重要事项 您必须按 CTRL+SHIFT+ENTER 键，将此公式输入为阵列。 以 abc123 这个字串为例，此公式将会产生 4— 也就是第一个数字字元在英数字元字串 中出现的位置。 计算英数字元字串中的数字总数 现在的任务是要计算字串中的数字总数，以决定除了英数字元字串中第一个数字以外，还 要传回第一个数字之後的多少个字元。 刚才我们曾经提过， 将英数字元字串中的数字储存为文字之後，只要将它们乘以 1，就 可以将它们转换为数字。例如： =1*MID(A1,ROW($1:$9),1) 将储存为文字的数字转换为数字之後，就可以使用 COUNT 函数来进行计算。只要输入以 下公式，就可以计算这些数字： =COUNT(1*MID(A1,ROW($1:$9),1)) 整合上述公式 现在我们将会使用 MID 函数来整合此公式的各个部份，如以下范例所示。 =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) 基本上，我们现在可以如此描述这个问题：判别英数字元字串 (位於储存格 A1 中) 第一 个数字出现的位置。传回第一个数字及其後方的数字。 如果要将传回的字元转换为数字，请将公式乘以 1 (此步骤或可省略；但如果您想要对结 果执行数学运算，则请务必执行此步骤)。以下就是要在储存格 B1 中输入的最终公式： =1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))) 重要事项 您必须按 CTRL+SHIFT+ENTER 键，将此公式输入为阵列。 其他范例 (图) http://office.microsoft.com/global/images/default.aspx?AssetID=ZA011549041028 如果要进一步测试此公式，请将下图中的资料输入空白工作表的储存格 A1:A7 中。 字串转换范例 在储存格 B1 中输入此公式，再使用「自动填满」将此公式复制到储存格 B2:B7 中 (记 得要按 CTRL+SHIFT+ENTER 键)。 值得一提的是，如果字串是 yur09875reew ，而您使用了乘以 1 的公式，则栏 B 中的结 果将会是 9875 而非 09875。这是因为 0*1=0，因此 0 将会遭到忽略，并传回 9875 这 个结果。如果您希望传回的结果是 09875，则请不要将最後的公式乘以 1。 --

※ 发信站: 批踢踢实业坊(ptt.cc) ◆ From: 218.164.59.188