本文介绍了如何提取单元格字符串中的六位数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一列包含数据:

纽约真的很好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)

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

09-21 15:09