Excel教程:一長串文字,如何將它們分別放入不同字段的列中?
?
作者:E圖表述來源:部落窩教育發(fā)布時間:2022-08-17 17:57:37點擊:4796
編按:
這個世界上不存在完美的文本處理方法,只有寫的完美的字符串。數據不規(guī)范,同事兩行淚。今天來給大家分享兩種解決不規(guī)范文本的方法,趕緊來看一看吧!
文本的提取相信大家或多或少都會一些,但是道高一尺魔高一丈,有時候總會遇到一些XX同事,拿著一些不規(guī)范的文本來挑戰(zhàn)你的極限。
比如下面這個:
需求是:要將這樣的文本快速地處理到excel中,excel的表頭是下面的樣子,準備的還挺齊全。
字符串格式、表頭字段都準備好了,而且后面的數據都是按這個規(guī)則排列,萬事俱備只欠東風呀!
閑話不多說,開干吧!
注意:方法是死的,人是活的,接下來的操作方法,是按照上面的文本串的規(guī)則來做——即5個英文的逗號間隔6個區(qū)域的內容。內容可以缺省,間隔符不能少。如果有不按規(guī)矩來的文本串,今天的正確答案便是你眼中的錯誤答案,“二次操作”肯定是少不了的。
我用函數和VBA兩種方式,做了兩個模板,下面就分享給大家,希望有相同需求的同學也可以盡快解決問題。
一:函數處理方法
函數處理,必須要有輔助的處理過程,然后把處理區(qū)的內容“粘貼為數值”到保存區(qū)中,按上圖布局。
1、在B4:J4單元格區(qū)域,輸入輔助列,指明文本字符串中的某一部分是字段對應的內容,沒有內容的字段不用填,如下:
2、在B7:J7單元格區(qū)域輸入函數=IF(B4="","",TRIM(MID(SUBSTITUTE($B$3,",",REPT(" ",100)),B4*100-99,100)))
向右拉至J7單元格,填充函數,就得到了區(qū)分好的內容,而且是不按順序提取的喲!!
3、最后再復制B7:J7,粘貼數值到下面的“數據保存區(qū)”就可以了。
【函數解析】
這個函數是一個經典的按分隔符取字符串的嵌套函數。
首先,使用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) = "" '''狀態(tài),因為沒有字符串可提取,所以也可以不寫此句
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圖表述;同時部落窩教育享有專有使用權。若需轉載請聯系部落窩教育。