Excel教程:一长串文字,如何将它们分别放入不同字段的列中?
作者:E图表述来源:部落窝教育发布时间:2022-08-17 17:57:37点击:4363
编按:
这个世界上不存在完美的文本处理方法,只有写的完美的字符串。数据不规范,同事两行泪。今天来给大家分享两种解决不规范文本的方法,赶紧来看一看吧!
文本的提取相信大家或多或少都会一些,但是道高一尺魔高一丈,有时候总会遇到一些XX同事,拿着一些不规范的文本来挑战你的极限。
比如下面这个:
需求是:要将这样的文本快速地处理到excel中,excel的表头是下面的样子,准备的还挺齐全。
字符串格式、表头字段都准备好了,而且后面的数据都是按这个规则排列,万事俱备只欠东风呀!
闲话不多说,开干吧!
注意:方法是死的,人是活的,接下来的操作方法,是按照上面的文本串的规则来做——即5个英文的逗号间隔6个区域的内容。内容可以缺省,间隔符不能少。如果有不按规矩来的文本串,今天的正确答案便是你眼中的错误答案,“二次操作”肯定是少不了的。
我用函数和VBA两种方式,做了两个模板,下面就分享给大家,希望有相同需求的同学也可以尽快解决问题。
一:函数处理方法
函数处理,必须要有辅助的处理过程,然后把处理区的内容“粘贴为数值”到保存区中,按上图布局。
1、在B4:J4单元格区域,输入辅助列,指明文本字符串中的某一部分是字段对应的内容,没有内容的字段不用填,如下:
2、在B7:J7单元格区域输入函数=IF(B4="","",TRIM(MID(SUBSTITUTE($B$3,",",REPT(" ",100)),B4*100-99,100)))
向右拉至J7单元格,填充函数,就得到了区分好的内容,而且是不按顺序提取的哟!!
3、最后再复制B7:J7,粘贴数值到下面的“数据保存区”就可以了。
【函数解析】
这个函数是一个经典的按分隔符取字符串的嵌套函数。
首先,使用REPT函数,制作100个空格的字符串,再使用SUBSTITUTE函数将原字符串中的英文逗号都替换成100个空格,
然后,使用MID函数结合我们刚才在第4行加的辅助列,提取从2*100、5*100…为第一位开始的字符串100位,因为空格中都有实际的字符串占位,所以这样就断出我们要的内容了,如果原字符串字数过多,可以调整空格的长度。
最后,用TRIM函数将字符串的左右两端空格去掉,即完成我们的工作了
二、VBA处理方法
其实,现实工作中,作者还是更喜欢用VBA来解决问题,还是上面的问题,分享给大家一段代码吧。
代码分享如下:
Sub 提取() '''工程命名语句
With Sheets("代码方法") '''使用《代码方法》工作表
If .[B3] = "" Then '''如果单元格B3为空
Exit Sub '''结束代码
Else '''如果单元格B3不为空的情况
s = .[B3] '''将单元格B3中的值,放入变量s
End If '''if语句的结束语句
a = .[B1000000].End(3).Row '''确定B列被操作的最末一行
If IsNumeric(.Range("B" & a)) = True Then '''为了得到序号,如果B列最后一个不为空的单元格是数值
xh = .Range("B" & a) + 1 '''说明有初始序号,则此时累加1即可
Else
xh = 1 '''说明没有初始序号,序号从1开始计数
End If
End With '''with语句的结束语句
ReDim arr(1 To 1, 1 To 9) '''定义一个1行9列的二维数组arr,装拆分后的数据使用
s1 = Split(s, ",") '''split函数拆分字符串,赋值到一个数组s1中,此时的s1是一维数组
'''注意:一维数组s1的初始序号是从0开始的
arr(1, 1) = xh '''序号列
arr(1, 2) = s1(1) '''下单时间
arr(1, 3) = s1(4) '''类型
arr(1, 4) = s1(0) '''姓名
arr(1, 5) = s1(2) '''手机
arr(1, 6) = s1(3) '''地址
arr(1, 7) = "" '''状态,因为没有字符串可提取,所以也可以不写此句
arr(1, 8) = "" '''完成时间,因为没有字符串可提取,所以也可以不写此句
arr(1, 9) = s1(5) '''备注
With Sheets("代码方法")
.Range("B" & a + 1).Resize(1, 9) = arr '''在B列被操作的最末一行的下面一行,将arr数组赋值到单元格
End With
Erase arr '''清空数组arr
Erase s1 '''清空数组s1
End Sub '''工程结束语句
最后保存的时候,记得把文件另存为.xlsm(启用宏的文件)即可完成了
【编后语】
无论是函数还是VBA,都会有它的长处和弊端,还是希望大家能够都涉猎一些,因为我们实际的工作中,不可能总是碰到像今天这样用两个方法都可以解决的问题的。
本文配套的练习课件请加入QQ群:902294808下载。
做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
版权申明:
本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。