按分隔符將文本字符串拆分為多行 - 3 個快速技巧
通常,您可以使用分欄功能將單元格內容按特定分隔符(例如逗號、點、分號、斜杠等)拆分為多列。但有時,您可能需要將分隔的單元格內容拆分為多行並重複其他列中的數據,如下圖所示。 您有什麼好的方法可以在 Excel 中處理此任務嗎? 本教程將介紹一些在 Excel 中完成這項工作的有效方法。
拆分文本以逗號、空格、分號等分隔。
要拆分由一些普通分隔符分隔的文本字符串,例如逗號、空格、分號、斜杠等,以下代碼可以幫你一個忙。 請按照以下步驟操作:
備註: 這個代碼 不 支持 復原,你最好在應用此代碼之前備份數據。
第 1 步:打開 VBA 模塊編輯器並複制代碼
1. 激活您要使用的工作表。 然後,按 Alt + F11鍵 鍵打開 Microsoft Visual Basic for Applications 窗口。
2. 在打開的窗口中,點擊 插入 > 模塊 創建一個新的空白模塊。
3. 然後,將以下代碼複製並粘貼到空白模塊中。
VBA 代碼:按特定分隔符(逗號、點、空格等)拆分文本
Sub SplitTextIntoRows()
'UpdatebyExtendoffice
Dim xSRg, xIptRg, xCrRg, xRg As Range
Dim xSplitChar As String
Dim xArr As Variant
Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
Dim xWSh As Worksheet
Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
If xSRg Is Nothing Then Exit Sub
xSplitChar = Application.InputBox("Type delimiter:", "Kutools for Excel", , , , , , 2)
If xSplitChar = "" Then Exit Sub
Application.ScreenUpdating = False
xRow = xSRg.Row
xColumn = xSRg.Column
Set xWSh = xSRg.Worksheet
For xFNum = xSRg.Rows.Count To 1 Step -1
Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
xArr = Split(xRg, xSplitChar)
xIndex = UBound(xArr)
For xFFNum = LBound(xArr) To UBound(xArr)
xRg.EntireRow.Copy
xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
xIndex = xIndex - 1
Next
xRg.EntireRow.Delete
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
第二步:執行代碼得到結果
1. 粘貼代碼後,請按 F5 鍵來運行這段代碼。 然後,會彈出一個提示框,提醒您選擇包含要拆分的分隔文本的單元格,請參見屏幕截圖:
2.然後,單擊 OK,會彈出另一個提示框,提醒您輸入拆分數據所依據的分隔符。 在這裡,我輸入一個逗號和一個空格 (, ),請看截圖:
3.最後,單擊 OK 按鈕。 現在,您將看到所選文本字符串根據逗號拆分為行,其他相關列數據重複如下圖所示:
拆分文本以換行符分隔
如果您的單元格內容由換行符分隔,將它們分成多行,這是另一個可以幫助您的 VBA 代碼。
備註: 這個代碼 不 支持 復原 您最好在應用此代碼之前備份數據。
第 1 步:打開 VBA 模塊編輯器並複制代碼
1。 按 Alt + F11鍵 鍵打開 Microsoft Visual Basic for Applications 窗口。
2. 在打開的窗口中,點擊 插入 > 模塊 創建一個新的空白模塊。
3. 然後,將以下代碼複製並粘貼到空白模塊中。
VBA代碼:按換行符拆分文本
Sub SplitTextIntoRows()
'UpdatebyExtendoffice
Dim xSRg, xIptRg, xCrRg, xRg As Range
Dim xSplitChar As String
Dim xArr As Variant
Dim xFNum, xFFNum, xRow, xColumn, xNum As Integer
Dim xWSh As Worksheet
Set xSRg = Application.InputBox("Select a range:", "Kutools for Excel", , , , , , 8)
If xSRg Is Nothing Then Exit Sub
xSplitChar = Chr(10)
Application.ScreenUpdating = False
xRow = xSRg.Row
xColumn = xSRg.Column
Set xWSh = xSRg.Worksheet
For xFNum = xSRg.Rows.Count To 1 Step -1
Set xRg = xWSh.Cells.Item(xRow + xFNum - 1, xColumn)
xArr = Split(xRg, xSplitChar)
xIndex = UBound(xArr)
For xFFNum = LBound(xArr) To UBound(xArr)
xRg.EntireRow.Copy
xRg.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown
xRg.Worksheet.Cells(xRow + xFNum, xColumn) = xArr(xIndex)
xIndex = xIndex - 1
Next
xRg.EntireRow.Delete
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
第二步:執行代碼得到結果
1. 粘貼代碼後,請按 F5 鍵來運行這段代碼。 在彈出的框中,選擇要拆分的單元格,見截圖:
2.然後,單擊 OK 按鈕,所選單元格中的數據被分成幾行,如下圖所示:
方法 B:10s 通過 Kutools for Excel 將分隔文本拆分為多行
如果你已經安裝 Excel的Kutools,其 將數據拆分為行 功能,您可以按指定的任何分隔符將文本字符串拆分為多行。 請按照以下步驟進行:
第 1 步:選擇“將數據拆分為行”功能
點擊 庫工具 > 合併與拆分 > 將數據拆分為行,請參見屏幕截圖:
第 2 步:指定拆分的數據單元格和分隔符
在彈出的對話框中,使用以下選項:
- 1). 選擇要從中拆分的單元格列表 範圍(單列) 文本框;
- 2). 然後,選擇分隔數據的分隔符,在這裡,我會選擇 其他 並在文本框中鍵入逗號和空格 (, );
- 3). 最後,點擊 OK 按鈕。
現在,帶有分隔文本字符串的選定單元格已根據特定分隔符轉換為多行,請參見屏幕截圖:
更多竅門:如果要恢復原始數據,只需按 按Ctrl + Z 用於撤消。
便於使用? 對此功能感興趣,請 點擊下載即可免費試用 30 天.
方法 C:將分隔文字拆分為多行 Power Query
如果您運行的是 Office 365 或 Excel 2016 及更高版本, Power Query 是一個強大的工具,可以幫助您將分隔的文本拆分為多行或多列。 如果您希望在原始數據更改時刷新拆分數據,這將很有用。 請按照以下步驟完成它:
第一步:將資料表放入 Power Query
1. 選擇您要使用的數據范圍,然後單擊 數據 > 從表格,請參見屏幕截圖:
更多竅門:在 Excel 2019 和 Office 365 中,單擊 數據 > 從表/範圍.
2.在彈出的 創建表格 對話框,單擊 OK 按鈕創建一個表,看截圖:
3。 現在 Power Query 編輯頁 窗口顯示數據,見截圖:
步驟 2:進行轉換 Power Query
1. 選擇要拆分的列。 然後,單擊 主頁 > 拆分列 > 按分隔符,請參見屏幕截圖:
2。 在裡面 按分隔符拆分列 對話框:
要用逗號、空格、分號等分割文本字符串,請按如下操作:
- 1). 選擇您的數據與 選擇或輸入分隔符 下拉列表;
- 2)。 選擇 每次出現的分隔符 來自 分裂於 部分;
- 3). 然後,點擊 高級選項 展開該部分,然後選擇 行 在 拆分成;
- 4). 最後,點擊 OK 關閉此對話框。
要通過鏈接中斷將文本字符串拆分為多行,請按照以下步驟操作:
- 1)。 選擇 客製化 來自 選擇或輸入分隔符 下拉列表;
- 2). 來自 插入特殊字符 下拉,點擊 換行, 和字符 #(如果) 將被插入到下面的文本框中 客製化 自動
- 3)。 選擇 每次出現的分隔符 來自 Split at 部分;
- 4). 然後,點擊 高級選項 展開該部分,然後選擇 行 在 拆分成;
- 5). 最後,點擊 OK 關閉此對話框。
3. 現在,所選數據已拆分為多行,如下圖所示:
第三步:輸出 Power Query 到 Excel 表格
1. 然後,您應該將數據輸出到您的工作表中。 請點擊 主頁 > 關閉並加載 > 關閉並加載 / 關閉並加載到,(在這裡,我將點擊 關閉並加載),請參見屏幕截圖:
更多竅門:點擊 關閉並加載 將數據輸出到新的工作表; 點擊 關閉並加載到 選項,數據將輸出到您需要的任何其他工作表。
2.最後,數據將被加載到一個新的工作表中,見截圖:
更多竅門:如果您需要經常更新原表中的數據,請不要擔心,只需右鍵單擊結果表並單擊 刷新 動態獲取新結果。
相關文章:
- 將單元格值拆分為多列
- 在 Excel 中,當您想通過某些分隔符(例如逗號、空格、句點、換行符等)將單元格值列表拆分為多列時,通常,內置的文本分列功能可以幫助您完成此工作步驟循序漸進。 這篇文章,我將討論一些好技巧,讓您輕鬆快速地將單元格值拆分為多列或多行。
- 將單元格拆分為多行或多列
- 假設您有一個單元格,其中包含由特定字符(例如,分號)分隔的多個內容,然後您要根據分號將此長單元格拆分為多行或多列,在這種情況下,您是否有任何快速方法在Excel中解決它?
- 按大小寫拆分文本字符串
- 在Excel中,我們通常使用“文本到列”功能按固定寬度或定界符拆分文本,但是您是否嘗試過按大寫和小寫拆分文本? 例如,您有一個數據列表,您需要根據大小寫將它們分為兩列:一列包含小寫字母的字符串,另一列包含大寫字母的字符串,如下面的屏幕截圖所示。 “文本到列”功能不支持此操作,但是,我可以介紹一些技巧,以便在Excel中按大小寫快速拆分文本字符串。
- 將數字拆分為單個數字
- 假設您需要將數字分解或分解為單個數字,如下面的屏幕截圖所示,您可以怎麼做? 本文將為您提供兩種方法。
最佳辦公生產力工具
🤖 | Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行 | 生成代碼 | 建立自訂公式 | 分析數據並產生圖表 | 呼叫 Kutools 函數... |
熱門特色: 尋找、突出顯示或識別重複項 | 刪除空白行 | 合併列或儲存格而不遺失數據 | 沒有公式的回合 ... | |
超級查詢: 多條件VLookup | 多值VLookup | 跨多個工作表的 VLookup | 模糊查詢 .... | |
高級下拉列表: 快速建立下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
欄目經理: 新增特定數量的列 | 移動列 | 切換隱藏列的可見性狀態 | 比較範圍和列 ... | |
特色功能: 網格焦點 | 設計圖 | 大方程式酒吧 | 工作簿和工作表管理器 | 會員專區 (自動文字) | 日期選擇器 | 合併工作表 | 加密/解密單元格 | 按清單發送電子郵件 | 超級濾鏡 | 特殊過濾器 (過濾粗體/斜體/刪除線...)... | |
前 15 個工具集: 12 文本 工具 (添加文本, 刪除字符,...) | 50+ 圖表 類型 (甘特圖,...) | 40+ 實用 公式 (根據生日計算年齡,...) | 19 插入 工具 (插入二維碼, 從路徑插入圖片,...) | 12 轉化 工具 (數字到單詞, 貨幣兌換,...) | 7 合併與拆分 工具 (高級合併行, 分裂細胞,...) | ... 和更多 |
使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。 點擊此處獲取您最需要的功能...
Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!