http://office.microsoft.com/zh-cn/excel-help/HA001154901.aspx
本文的作者是 Ashish Mathur，是一位 Microsoft MVP（最有价值专家）。有关详细信息，请访问 Microsoft MVP 网站（英文）。
在下文中，我将说明从以下各种情况中的字符串内提取数字的公式：
- 当字母字符和数字字符连在一起时，如
abc123或678sfr。 - 当字母字符和数字字符没有连在一起时，如
abc15tni。
问题
如何提取字母数字字符串的数字部分。例如：如果单元格 A1 包含的是字符串“abc123”，则将值 123 返回单元格 B1 中。
解决方案
此解决方案的基本原理是搜索并返回字母数字字符串中的第一个数字，然后只返回其后的数字。
算法
此解决方案包括创建公式以完成下列任务：
- 将字母数字字符串分解为单独的字符。
- 确定分解后的字符串中是否有数字。
- 确定数字在字母数字字符串中的位置。
- 计算字母数字字符串中数字的数量。
我们将分别考虑这些任务，然后将各公式整合在一起以得到最终结果。
将字母数字字符串分解为单独的字符
请在此使用 MID 函数。MID 可以根据所指定的字符的数量，从所指定的位置开始，从文本字符串中返回特定数量的字符。此函数的语法是：
MID(text,start_num,num_chars)
- Text 文本字符串包含的是要提取的字符。
- Start_num 要从文本中提取的第一个字符串的位置。文本中第一个字符占据 start_num 1，以此类推。
- Num_chars 指定要 MID 从文本中返回的字符数量。
对于我们的示例，公式为：
=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 将这些公式作为数组进行输入。
更多示例
要进一步测试此公式，请将下图中的数据输入空工作表中的单元格 A1:A7 中。
将此公式输入单元格 B1 中，然后使用自动填充将此公式复制到单元格 B2:B7 中。（不要忘记按 Ctrl+Shift+Enter。）
在此值得一提的是，如果字符串是 yur09875reew，而且您使用的是已乘 1 的公式，则列 B 中的结果将是 9875 而不是 09875。因为 0*1=0，所以 0 被忽略了，返回的结果是 9875。如果想得到结果 09875，则不要用 1 乘以整个公式。
=-===========================
excel，index和match函数
MATCH(要查找的内容,查找的区域,精确查找或近似查找)
这个函数
可以返回要查找的内容在查找区域中的位置,参数0为精确查找,参1为近似查找
如
=MATCH(B7,A2:A4,0) 按图中得出的值应为2
意思就是在A2至A4单元格区域中精确查找与B7单元格内容相同的单元格在这列区域中是第几个
=MATCH(A7,B1:D1,0) 按图中得出的值应为3
意思就是在B1至D1单元格区域中精确查找与A7单元格内容相同的单元格在这行区域中是第几个
INDEX(待返回值所在的单元格区域,第某行,第某列)
这个函数可以返回在指定区域中第某行第某列所在的单元格内容
如
=INDEX(B2:D4,2,3) 按图中得出的值应为D3单元格内容即40
在B2单元格至D4单元格中的第二行就是表格中的第三行,第三列就是D列,所以值为D3
如,取A列单元格区域中第5行的值,则公式为
=INDEX(A:A,5) 即返回A5单元格内容
如取第二行单元格中第10列的值,公式为
=INDEX(2:2,10) 即返回J2单元格内容
如在A列至G列行数为1至100行的单元格区域中找第23行,第4列单元格的内容,则公式为
=INDEX(A1:G100,23,4) 即返回D23单元格的内容
$是绝对引用符号
A1相对引用
$A1绝对引用列
A$1绝对引用行
$A$1绝对引用行和列
$在谁的前面就绝对引用谁
F4是在四种引用间相互转换的快捷键(在编辑栏输入公式时按下F4功能键可进行切换)
相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。
具体情况举例说明：
1、相对引用，复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1
当将公式复制到C2单元格时变为：=A2+B2
当将公式复制到D1单元格时变为：=B1+C1
2、绝对引用，复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1
当将公式复制到C2单元格时仍为：=$A$1+$B$1
当将公式复制到D1单元格时仍为：=$A$1+$B$1
3、混合引用，复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1
当将公式复制到C2单元格时变为：=$A2+B$1
当将公式复制到D1单元格时变为：=$A1+C$1
规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。
http://office.microsoft.com/zh-cn/excel-help/HP010342940.aspx?CTT=1
http://office.microsoft.com/zh-cn/mac-excel-help/HA102928059.aspx?CTT=1
如果你要用VLOOKUP函数,则公式可以写成
=VLOOKUP(A7,A$2:$D$4,MATCH(B7,B$1:D$1,)+1,)
或
=VLOOKUP(A7,A$2:$D$4,MATCH(B7,A$1:D$1,),)
==============
在相对引用、绝对引用和混合引用间切换
全部显示
全部隐藏默认情况下，单元格引用是相对 （相对引用:在公式中，基于包含公式的单元格与被引用的单元格之间的相对位置的单元格地址。如果复制公式，相对引用将自动调整。相对引用采用 A1 样式。）的。例如，当您引用单元格 A2 到单元格 C2，实际上引用的是左边的两列（C 减去 A）和同一行 (2) 中的单元格。包含相对单元格引用的公式会因为您将它从一个单元格复制到另一个而发生改变。例如，如果您将单元格 C2 中公式“=A2+B2”复制到 D2，D2 中的公式将向下调整一行成为“=A3+B3”。如果希望在复制时保留此示例中的原始单元格引用，需要在列（A 和 B）和行 (2) 之前加上美元符号 ($) 来使单元格引用变为绝对 （绝对单元格引用:公式中单元格的精确地址，与包含公式的单元格的位置无关。绝对引用采用的形式为 $A$1。）。然后，当您从 C2 复制公式 (=$A$2+$B$2) 到 D2，该公式仍然是完全相同。
在不频繁的情况下，您可能希望使单元格引用变为“混合”，在前面的列值或行值之前加美元符号以“锁定”列或行（例如，$A2 或 B$3）。若要更改单元格引用的类型：
- 选择包含公式的单元格。
- 在编辑栏 编辑栏:位于 Excel 窗口顶部的条形区域，用于输入或编辑单元格或图表中的值或公式。编辑栏中显示了存储于活动单元格中的常量值或公式。
中，选择要更改的引用。 - 按 F4 在引用类型之间切换。
下表总结了当将包含引用的公式向下和向右复制两个单元格时引用类型的更新方式。
| 对于正在复制的公式： | 如果引用是： | 它会更改为： |
|---|---|---|
 | $A$1（绝对列和绝对行） | $A$1（引用是绝对的） |
| A$1（相对列和绝对行） | C$1（引用是混合型） | |
| $A1（绝对列和相对行） | $A3（引用是混合型） | |
| A1（相对列和相对行） | C3（引用是相对的） |
==============
http://www.111cn.net/office/excel/50362.htm
LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”) 获取字符串中空格的个数
SUBSTITUTE(A2, ” “, “-”, step1) 将最后一个空格替换为”-”
FIND(“-”, step2) 找到最后一个空格的位置
RIGHT(A2, LEN(A2) – step3) 返回最后一个空格之后的字符串
IF(ISERROR(FIND(” “, A2)), A2, step4) 字符串中没有空格时不做处理，避免出现#VALUE!的结果
利用上面的函数我们来看一个查找字符串中*星号位置并取出
所以，要想查找字符串中的，*星号和?问号，必须在其前面加上这样的一个符号 ~ 方能找到。
上表中，每个单元格都包含两个*星号，同时还包含三组数字，但是，每组数字的长度都不一样，有的长有的短。现在的问题是，如何把每个单元格中被*星号隔开的每数字分别取出来？
一、问题的分析
要想取出被两个*星号隔开的三组数字，关键的问题在于，如何知道并获取两个*星号分别处于字符串中的哪个位置。
知道两个*星号的位置后，我们就可以使用截取函数将三组数字分别取出来。
二、获取两个*星号的位置
获取某个字符在字符串中所处的位置，可以使用SEARCH和SEARCHB函数来查找位置。
两个函数的语法相同，如下：
SEARCH(find_text,within_text,start_num)
SEARCHB(find_text,within_text,start_num)
不过，两个函数的区别在于，前者在查找的时候，把一个汉字的长度当作一个字符，后者则是以字节的方式来查找，一个汉字当两个字节，所以，一个汉字的长度就为2。一般情况下，在中文英文数字特殊字符混合的字符串中查找，应该使用SEARCH函数。
把以上两个函数翻译成中文，其语法为：
SEARCH(要找的字符,包含字符的字符串,从串中的第几个位置开始找)
SEARCHB(要找的字符,包含字符的字符串,从串中的第几个位置开始找)
有了如上的函数基础与理论指导，下面，我们开始查找两个星号的位置吧。
①第一个*星号的位置
通过函数=SEARCH("~*",A1)即可获取第一个星号的位置，处于第5位。
②第二个*星号的位置
第二个*星号的位置查找，就比较困难了，公式有点绕口令的感觉。
总之，公式如下：=
| 代码如下 | 复制代码 |
| SEARCH("~*",A1,SEARCH("~*",A1)+1) | |
看到了吧？上述的公式比较复杂，但是，却也很好理解。
通过公式=SEARCH("~*",A1)可获得第一个位置，那么，再第一个位置的基础上加1，从这个位置开始继续往下找，就可以找到第二星号的位置，所以，公式就是上图中稍微绕口的函数了。
三、从带有星号的字符串中提取数字
有了一二的基础，现在，我们该是提取数字了。
①提前第一个星左边的数字公式
| 代码如下 | 复制代码 |
| =LEFT(A1,SEARCHB("~*",A1)-1) | |
②提取中间那个数字的公式
| 代码如下 | 复制代码 |
| =MID(A1,SEARCHB("~*",A1)+1,(SEARCHB("~*",A1,SEARCHB("~*",A1)+1))-SEARCHB("~*",A1)-1) | |
==========================result
{=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1))+(LEN(A1)-LEN(SUBSTITUTE(A1,".", ""))))}
2370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
