如何提取单元格字符串中的六位数字? [英] How to extract the six digit number in a cell string?

问题描述

我有一列数据:

纽约真的很好，456983 好吃的
北京真棒678932，好文化.
...等等

New York is really nice, 456983 Good food
Beijing is awesome 678932, Nice culture.
... and so on

我想提取六位数字并将其放在原始列旁边的列中.

I want to extract the six digit numbers and place it in a column beside the original column.

有 n 行.

有没有办法用公式来做到这一点，不用 VBA?

Is there any way to do this with a formula, without VBA?

推荐答案

如果没有 VBA，您可以使用以下 Excel 公式，假设 A1 是包含文本的单元格.

Without VBA, you can use the following Excel formula assuming A1 is the cell containing the text.

=MID(A1,FIND("------",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","-"),"1","-"),"2","-"),"3","-"),"4","-"),"5","-"),"6","-"),"7","-"),"8","-"),"9","-")),6)

这篇关于如何提取单元格字符串中的六位数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！