返回目录:office365
怎么在Excel里提取指定数字?
公式如图。
说明:row(1:100)是循环的意思,假定字符串最长为100,依次取值1到100;
mid(a1,row(1:100),1)将字符串一次拆分为单个字符;
mid(a1,row(1:100),1)+0即将数字字符转换为数值,非数字字符则显示错误;
iferror(mid(a1,row(1:100),1)+0,"")即将上面错误但字符转为空值;
最后textjoin("",1,iferror(mid(a1,row(1:100),1)+0,""))即将单个字符都连起来并且忽略空值,textjoin第一参数为连接符,""表示不要任何连接符,1表示忽略空白。
最后按【Ctrl+Shift+Enter】三键一起按下,表示输入数组公式。
编辑2:
下面回答的【快速填充】真是完美解决,顺便说一句快速填充快捷键【Ctrl+E】
顺便安利一下 神奇的Excel快速填充功能,到底有多牛逼
https://www.jianshu.com/p/63cb15028f86
鉴于Excel2016以下及2016部分版本没有Textjoin代码,遂附上自定义替代函数Txtjoin(分隔符,是否忽略空格,要链接的字符串),操作跟上面一样一样
Function Txtjoin(split As String, ignore_blank As Boolean, a)
Dim d, c, str As String, isplit As String
Set d = CreateObject("scripting.dictionary")
For Each c In a
d("" & c) = ""
If "" & c = "" Then
If ignore_blank Then isplit = ""
End If
str = str & isplit & c
isplit = split
Next
Txtjoin = str
End Function
Excel中如何将干净混合数据中的汉字和数字分离?
情景设定:
对于分离混合数据中的汉字和数字,我们首先设定一个比较常见的情景:当前有一列企业银行账号数据,每行数据由一串汉字和一串数字组成,汉字与数字的位数均不固定,要求将汉字和数字分别拆分至两列。
问题思考:
①对于该类问题大部分人第一反应是使用函数来完成,但这个问题明显要进行多层嵌套,涉及字符提取/判断等多种函数,逻辑复杂且适用范围窄;
②换个思路可以用Ctrl+E快速填充功能来实现,但这个功能只适用于比较简单的数据提取,当提取规则比较复杂时,出错概率大大增加;
③分列功能适用范围更窄明显无法解决本问题;
④VBA对编程基础有一定要求不适用于普遍用户;
经过层层筛选,我选择使用专职于数据整理的PowerQuery功能来解决本问题。
Step1.将数据加载到PowerQuery中
全选数据区域,依次点击“数据”选项卡——“从表格”。
进入PowerQuery界面如下:
Step2.插入自定义列提取银行名称和银行账号
打开“视图”选项卡下面的“高级编辑器”,如图:
在高级编辑器中编写两条自定义列的M语句,分别用于提取银行名称和银行账号,全部代码如下:
let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
更改的类型 = Table.TransformColumnTypes(源,{{"企业银行帐号", type text}}),
提取银行名称= Table.AddColumn(更改的类型, "银行名称", each Text.Remove([企业银行帐号],{"0".."9"})),
提取银行账号 = Table.AddColumn(提取银行名称, "银行账号", each Text.Remove([企业银行帐号],{"一".."龢"}))
in
提取银行账号
点击完成后结果如下:
Step3.将结果返回工作表
点击“关闭并上载至”,将结果以表的形式返回至工作表中。
更厉害的是,后续原始数据如果有更新,直接点击“数据”选项卡下面的“全部刷新”即可瞬间自动得到最新的提取结果。
「精进Excel」系头条签约作者,关注我,如果任意点开三篇文章,没有你想要的知识,算我耍流氓!