小伙伴們好啊，今天咱們來(lái)分享一個(gè)字符拆分的公式用法。如下圖所示，A 列是一些混合內(nèi)容，同一個(gè)單元格內(nèi)包含有多級(jí)科目名稱，不同科目之間使用“/”間隔，需要將這些科目分別拆分到右側(cè)的各列里。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡(jiǎn)體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語(yǔ)言：簡(jiǎn)體中文
• 時(shí)間：2024-09-12

查看詳情

方法一：

B2 單元格輸入以下公式，將公式向右向下復(fù)制。

=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",99)),COLUMN(A1)*99-98,99))

這是一個(gè)典型的字符拆分模式化用法，接下來(lái)咱們說(shuō)說(shuō)這個(gè)公式的意思。

REPT函數(shù)的作用是根據(jù)指定的次數(shù)重復(fù)顯示字符。

TRIM函數(shù)的作用是清除字符中的多余空格。

COLUMN 函數(shù)的作用是返回參數(shù)的列號(hào)，如果省略參數(shù)，則返回公式所在單元格的列號(hào)。

本例中的“COLUMN(A1)*99-98”部分，先使用 COLUMN 函數(shù)返回 A1 單元格的列號(hào)1，再用 COLUMN 函數(shù)的結(jié)果乘以 99 減 98，即 1*99-98，結(jié)果仍然是 1。

當(dāng)公式向右復(fù)制時(shí)，COLUMN 函數(shù)會(huì)依次得到 B1、C1……的列號(hào)，再將這些列號(hào)乘以 99 減 98，即相當(dāng)于 2*99-98、3*99-98……，最終得到按 99 遞增的序號(hào) 1、100、199……，以此作為 MID函數(shù)的第二參數(shù)。

SUBSTITUTE 函數(shù)的作用是將字符串中的指定字符替換為新的字符，函數(shù)用法為：

SUBSTITUTE(需要處理的文本,要替換掉哪些,要替換成啥,[要替換第幾個(gè)])

公式中的“SUBSTITUTE($A2,"/",REPT(" ",99))”部分，先使用“REPT(" ",99)”將空格重復(fù) 99 次，最終得到 99 個(gè)空格。

再使用 SUBSTITUTE 函數(shù)將 A2 單元格中的每一個(gè)分隔符“/”都替換為 99 個(gè)空格，使其變成以下樣式的新字符串：

"管理費(fèi)用 稅費(fèi) 水利建設(shè)資金"

REPT(" ",99)的作用，就是增加原有字符串中各個(gè)科目之間的間隔寬度，以便于 MID 函數(shù)分段截取出帶空格的字符。

接下來(lái)使用 MID 函數(shù)，從 SUBSTITUTE 函數(shù)返回的字符串中提取字符，提取的起始位置是“COLUMN(A1)*99-98”得到的序號(hào) 1，提取長(zhǎng)度為 99 個(gè)字符，結(jié)果為帶有多余空格的內(nèi)容：

"管理費(fèi)用 "

最后使用 TRIM 函數(shù)清除字符串中的多余空格，得到一級(jí)科目名稱“管理費(fèi)用”。

當(dāng)公式向右復(fù)制時(shí)，MID 函數(shù)分別從 SUBSTITUTE 函數(shù)返回字符串中的第 1 位、第100 位、第 199 位……依次提取出 99 個(gè)字符，并使用 TRIM 函數(shù)清除多余空格，最終得到不同級(jí)別的科目名稱了。

方法二：

如果你使用的是Excel 2021或者最新版的WPS表格，只要B2單元格輸入以下公式，向下復(fù)制到B10即可。

=TEXTSPLIT(A2,"/")

TEXTSPLIT函數(shù)，是專門按指定間隔符號(hào)來(lái)拆分字符的。第一個(gè)參數(shù)是要拆分的內(nèi)容，第二參數(shù)是指定的間隔符號(hào)。

借助高版本中的動(dòng)態(tài)溢出功能，在B2單元格輸入公式后，結(jié)果會(huì)自動(dòng)溢出到右側(cè)的相鄰區(qū)域，就這么簡(jiǎn)單。

總結(jié)：數(shù)據(jù)分列和快速填充也能拆分有分隔規(guī)律的字符，但是這兩個(gè)方法和公式相比都有一定的局限性，你能說(shuō)說(shuō)局限性有哪些嗎？

推薦閱讀：Excel中神奇的拆分函數(shù)! 一文講透TextSplit函數(shù)從入門到精通

最新評(píng)論