如何提取单元格字符串中的六位数? [英] 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屋！