字符汉字数字【提取】
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
提取数字
例：分别提取下列单 元格中的数字、英文 、中文
12.3Invtid存货/代码 00175Transfer移出 10000bb宝贝 Begin期初4.5 PM Receipt生产(入库)018 PM Sentout出库35 PP8.0采购入库 PO Receipt0188采购/其他入库 Transfer1245移入
字在文本串中位置不确定“的 公式较left或right复杂
lookup数组形式 结果为数值 12.3 00175 10000 4.5 018 35 8.0 0188 1245
个人认为自定义公 式是提取英文、中 文字最好的方法。 但本例子主要研究 match、lookup查找 方法，因此自定义 公式仅作一种方法 分享，不作讨论。
：经测试，使用lookup的数组形式，就只能用“大 ”，左边框框讨论的“00式”、“10式”、“11式” 能使用，会报错。
个人认为自定义公 式是提取英文、中 文字最好的方法。 但本例子主要研究 match 方法，因此自定义 公式仅作一种方法 分享，不作讨论。
提取英文
分解公式 自定义公式 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? mid(B,N,O-N) Invtid Transfer bb Begin PM Receipt PM Sentout PP PO Receipt Transfer match取得首字母的 位置 5 6 6 1 1 1 1 1 1
本次思考总结： 1.从数列中返回第一个或最后一个满足条件的位置或者
2.模糊查找的方式有几个（10、00、11、大数、大数0/
lookup或match取得末 中文字的位置 15 15 9 7 15 12 9 21 14 10式 正确 00式 错误 12 15 9 7 12 12 9 16 14 match 11式 错误 大数式 错误 大数0/条件式 正确 10式 正确 00式 错误 12 15 9 7 12 12 9 16 14 15 15 9 10 19 14 9 21 14 15 15 9 7 15 12 9 21 14 15 15 9 7 15 12 9 21 14 12 15 9 7 12 12 9 16 14 lookup
回最后一个符合
处模糊查找不是跟 ）都能返 测试，只有10式和 确位置，而且错误 ） 透彻理解，但可以
或最后一个满足条件的位置或者值，分别用精确查找和模糊查找
几个（10、00、11、大数、大数0/条件等等），要根据不同情况选择，还有经过多次测试才能慎重选
12 15 9 7 12 12 9 16 14 lookup 11式 错误
同是使用lookup公式的矢量形式，但lookup_value和 lookup_vector使用的参数有不同，C列用的是在“0/ （条件）”中查找“0”，D列用的是在“--（条件）” 中查找9E+306，返回的结果是完全一样的。
经过多次测试，以下lookup公式（矢量形式）返回的结 果是一样的： lookup（，0/（条件），返回值）；（00式） lookup（1，0/（条件），返回值）；（10式） lookup（1，1/（条件），返回值）；（11式） lookup（9E+307，--（条件），返回值）；（大数式） 值得注意的是： lookup（1，1/（条件），返回值），（01式）是不能 用的，会报错。
lookup或match模糊查找返回最后一个符合 条件的值的位置 我在试算过程中发现，此处模糊查找不是跟 前面一样，每个方式（00、11……）都能返 回正确的结果，经过右表测试，只有10 大数0/条件式才能返回正确位置，而且错误 的位置各有小差异（黑色highlight） 对于这个现象，本人还未透彻理解，但可以 总结出以下经验
15 15 9 10 19 14 9 21 14 大数式 错误
15 15 9 7 15 12 9 21 14 大数0/条件式 正确
多次测试才能慎重选择某个正确方式
mid，用于”数字在文本串中位置不确定“的 情况，当然公式较left或right复杂
lookup矢量形式 结果为文本串 12.3 175 10000 4.5 18 35 8 188 1245
lookup公式的9E+307方法，D列用的是矢量形式， 用的是数组形式，区别在于返回的结果，分别是文本 和数值形式，使用时根据需要选择。
left，用于确定“数字都在文本串最前”的情况
lookup矢量形式 结果为文本串 12.3 00175 10000 #N/A #N/A #N/A #N/A #N/A #N/A 12.3 00175 10000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A lookup数组形式 结果为数值 12.3 175 10000
提取中文
lookup或match取得末 字母的位置 10 13 7 5 10 10 2 10 8
自定义公式 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
mid(B,R,S-R) 存货/代码 移出 宝贝 期初 生产(入库 出库 采购入库 采购/其他入库 移入
match取得首中文 字的位置 11 14 8 6 11 11 6 15 13
首字母的位置）
Βιβλιοθήκη Baidu长度
条件的位置，但在此处要返回第一个 查找的方法： T("1:"&LEN(B4))),1)<="z")*(MID(B4,ROW(
返回的值是数组{0,0,0,0,1,1……}，所 返回第一个符合条件的值的位置。
同是使用lookup公式的 E列用的是数组形式，区别在于返回的 形式和数值形式，使用时根据需要选择 提示：经测试，使用 数式”，左边框框讨论的“ 都不能使用，会报错。
提取数字（lookup）
right，用于确定“数字都在文本串最后”的情况
lookup矢量形式 结果为文本串 #N/A #N/A #N/A 4.5 018 35 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 4.5 4.5 18 018 35 35 #N/A #N/A #N/A lookup数组形式 结果为数值 #N/A #N/A #N/A 12.3 00175 10000 4.5 018 35 8.0 0188 1245
=mid(单元格，首字母的位置，末字母的位置—首字母的位置）
长度
这里必须用match公式 match模糊查找返回最后一个符合条件的位置，但在此处要返回 没有报错的值的位置，因此使用了精确查找的方法： =MATCH(1,((MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)<="z")*(MID(B4,ROW( INDIRECT("1:"&LEN(B4))),1)>="a")),0) 思路分析：(mid()<="z")*(mid()>="a")返回的值是数组 以match(1,{0,0,0,0,1,1……},0)就能返回第一个符合条件的值的位置。
例：分别提取下列单 元格中的数字、英文 、中文
12.3Invtid存货/代码 00175Transfer移出 10000bb宝贝 Begin期初4.5 PM Receipt生产(入库)018 PM Sentout出库35 PP8.0采购入库 PO Receipt0188采购/其他入库 Transfer1245移入
字在文本串中位置不确定“的 公式较left或right复杂
lookup数组形式 结果为数值 12.3 00175 10000 4.5 018 35 8.0 0188 1245
个人认为自定义公 式是提取英文、中 文字最好的方法。 但本例子主要研究 match、lookup查找 方法，因此自定义 公式仅作一种方法 分享，不作讨论。
：经测试，使用lookup的数组形式，就只能用“大 ”，左边框框讨论的“00式”、“10式”、“11式” 能使用，会报错。
个人认为自定义公 式是提取英文、中 文字最好的方法。 但本例子主要研究 match 方法，因此自定义 公式仅作一种方法 分享，不作讨论。
提取英文
分解公式 自定义公式 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? mid(B,N,O-N) Invtid Transfer bb Begin PM Receipt PM Sentout PP PO Receipt Transfer match取得首字母的 位置 5 6 6 1 1 1 1 1 1
本次思考总结： 1.从数列中返回第一个或最后一个满足条件的位置或者
2.模糊查找的方式有几个（10、00、11、大数、大数0/
lookup或match取得末 中文字的位置 15 15 9 7 15 12 9 21 14 10式 正确 00式 错误 12 15 9 7 12 12 9 16 14 match 11式 错误 大数式 错误 大数0/条件式 正确 10式 正确 00式 错误 12 15 9 7 12 12 9 16 14 15 15 9 10 19 14 9 21 14 15 15 9 7 15 12 9 21 14 15 15 9 7 15 12 9 21 14 12 15 9 7 12 12 9 16 14 lookup
回最后一个符合
处模糊查找不是跟 ）都能返 测试，只有10式和 确位置，而且错误 ） 透彻理解，但可以
或最后一个满足条件的位置或者值，分别用精确查找和模糊查找
几个（10、00、11、大数、大数0/条件等等），要根据不同情况选择，还有经过多次测试才能慎重选
12 15 9 7 12 12 9 16 14 lookup 11式 错误
同是使用lookup公式的矢量形式，但lookup_value和 lookup_vector使用的参数有不同，C列用的是在“0/ （条件）”中查找“0”，D列用的是在“--（条件）” 中查找9E+306，返回的结果是完全一样的。
经过多次测试，以下lookup公式（矢量形式）返回的结 果是一样的： lookup（，0/（条件），返回值）；（00式） lookup（1，0/（条件），返回值）；（10式） lookup（1，1/（条件），返回值）；（11式） lookup（9E+307，--（条件），返回值）；（大数式） 值得注意的是： lookup（1，1/（条件），返回值），（01式）是不能 用的，会报错。
lookup或match模糊查找返回最后一个符合 条件的值的位置 我在试算过程中发现，此处模糊查找不是跟 前面一样，每个方式（00、11……）都能返 回正确的结果，经过右表测试，只有10 大数0/条件式才能返回正确位置，而且错误 的位置各有小差异（黑色highlight） 对于这个现象，本人还未透彻理解，但可以 总结出以下经验
15 15 9 10 19 14 9 21 14 大数式 错误
15 15 9 7 15 12 9 21 14 大数0/条件式 正确
多次测试才能慎重选择某个正确方式
mid，用于”数字在文本串中位置不确定“的 情况，当然公式较left或right复杂
lookup矢量形式 结果为文本串 12.3 175 10000 4.5 18 35 8 188 1245
lookup公式的9E+307方法，D列用的是矢量形式， 用的是数组形式，区别在于返回的结果，分别是文本 和数值形式，使用时根据需要选择。
left，用于确定“数字都在文本串最前”的情况
lookup矢量形式 结果为文本串 12.3 00175 10000 #N/A #N/A #N/A #N/A #N/A #N/A 12.3 00175 10000 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A lookup数组形式 结果为数值 12.3 175 10000
提取中文
lookup或match取得末 字母的位置 10 13 7 5 10 10 2 10 8
自定义公式 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
mid(B,R,S-R) 存货/代码 移出 宝贝 期初 生产(入库 出库 采购入库 采购/其他入库 移入
match取得首中文 字的位置 11 14 8 6 11 11 6 15 13
首字母的位置）
Βιβλιοθήκη Baidu长度
条件的位置，但在此处要返回第一个 查找的方法： T("1:"&LEN(B4))),1)<="z")*(MID(B4,ROW(
返回的值是数组{0,0,0,0,1,1……}，所 返回第一个符合条件的值的位置。
同是使用lookup公式的 E列用的是数组形式，区别在于返回的 形式和数值形式，使用时根据需要选择 提示：经测试，使用 数式”，左边框框讨论的“ 都不能使用，会报错。
提取数字（lookup）
right，用于确定“数字都在文本串最后”的情况
lookup矢量形式 结果为文本串 #N/A #N/A #N/A 4.5 018 35 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 4.5 4.5 18 018 35 35 #N/A #N/A #N/A lookup数组形式 结果为数值 #N/A #N/A #N/A 12.3 00175 10000 4.5 018 35 8.0 0188 1245
=mid(单元格，首字母的位置，末字母的位置—首字母的位置）
长度
这里必须用match公式 match模糊查找返回最后一个符合条件的位置，但在此处要返回 没有报错的值的位置，因此使用了精确查找的方法： =MATCH(1,((MID(B4,ROW(INDIRECT("1:"&LEN(B4))),1)<="z")*(MID(B4,ROW( INDIRECT("1:"&LEN(B4))),1)>="a")),0) 思路分析：(mid()<="z")*(mid()>="a")返回的值是数组 以match(1,{0,0,0,0,1,1……},0)就能返回第一个符合条件的值的位置。