巧妙利用LEN、SUBSTITUTE函數,計算EXCEL單元格內某重複字符個數

2020-12-11 VBA語言專家

實際的工作中，我們有時候會用到求某個單元格內某個字符個數的實際問題。對於這類問題，在深入的學習了EXCEL函數之後，或者看過我寫的部分文章後，一般會很容易的給出解決答案，但有時候一些朋友往往還不能舉一反三的應用，還回頭來求助於我。為此，一些函數的應用我可能會有重複講解的時候，還望掌握好的朋友多諒解。

今天就是應一位朋友的反饋再講一次某些函數組合的應用。這位朋友提出的問題是：如何很快的計算出某單元格內某個重複字符的個數。對於這個問題，講解中會用到LEN函數和SUBSTITUTE函數，

老規矩，我們還是先複習一下這兩個函數的用法。

一 LEN函數 LEN函數作用是返回文本串的字符數。語法是Len (string)，是string類型的變量，返回值是Long型的值。發生錯誤時返回-1。如果任何參數的值為NULL，則Len()函數返回NULL。同時注意：LENB函數的功能為返回文本字符串中用於代表字符的字節數。

二 SUBSTITUTE函數 什麼是SUBSTITUTE函數呢？就是在文本字符串中用new_text 替代old_text的函數。語法：SUBSTITUTE(text,old_text,new_text,[instance_num])。Text 為需要替換其中字符的文本，或對含有文本的單元格的引用，Old_text 為需要替換的舊文本，New_text 用於替換 old_text 的文本。Instance_num 為一數值，用來指定以new_text 替換第幾次出現的 old_text。如果指定了 instance_num，則只有滿足要求的 old_text 被替換；如果預設則將用 new_text 替換 TEXT 中出現的所有 old_text。

好了，複習完了上面兩個函數的定義，我們來看今日實際問題的解決思路；對於某個單元格，某個字符是重複出現的，那麼我們把這個字符替換掉，然後用這個單元格的原長度減去替換後的長度，其結果就是這個所求重複字符的個數了。就是這麼簡單。

下面我們來實現這個過程.如下圖，

在A列的單元格中有很多的字符，要求計算B列中出現的字符的個數，並計入C列。

由於上面已經講了思路，這裡就直接看公式的截圖：

公式講解：C2中的公式：=LEN(A2)-LEN(SUBSTITUTE(A2,B2,""))

LEN(A2) 就是原字符串的長度；

SUBSTITUTE(A2,B2,"") 就是把A2單元格中的B2單元格內的字符替換為了空值。

LEN(SUBSTITUTE(A2,B2,"")) 就是替換後的長度

LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")) 兩者相減就是B2內字符在A2內出現的個數了。看下面的返回結果：

細心的讀者可以數一數。完全正確的。到此，問題圓滿的解決，這裡只是舉了個很簡單的例子，如果是求兩個單元格的內的重複字符數該怎麼去做呢？也好辦，可以先把這兩個單元格先合起來，再計算就可以了。別忘了，合併的符號是「&」。其他類似的問題可以同樣的處理。

總之，函數的學習是靈活多樣的，不要墨守成規，我也多次的講過，某個函數的作用是單一的，但只要是兩個或多個函數的組合，其功用是無法想像的。要多思考。多練習。

今日內容技巧回顧：

1 LEN函數和LENB函數的區別是什麼

2 SUBSTITUTE函數的作用是什麼，其三個參數的意義是什麼？

3 求重複字符的個數的思路是什麼？

分享成果，隨喜正能量

相關焦點

  • excel中的替代函數——replace和substitute函數的應用實例
    在excel中，常用的替換函數有replace和substitute函數，這兩個函數都可以替換單元格中的部分內容，功能和ctrl+H的功能類似，但是使用函數的目的一方面不會破壞原數據，另一方面與其他函數結合可以實現更多功能，對於substitute的參數=substitute（單元格，被替換的字符串，新字符串，指定替換第幾個），第四個參數可以省略
  • Excel教程:substitute函數的運用【輕鬆易學】
    substitute函數是屬於什麼函數，如何使用呢?substitute函數有點類似於excel中的查找替換命令，但卻更靈活好用。　　substitute函數屬於文本查找類函數，就是查找某個字符，然後替換成別的字符。
  • 使用excel函數substitute,len,left等函數來實現時間的替換轉換
    C3單元格的公式內容為：=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,"小時",":"),"分",":"),"秒","")而excel函數substitute就是替換的意思，這個函數，可以把單元格中所有的指定內容替換為需要的內容。
  • EXCEL函數公式大全之利用FIND函數和RIGHT函數LEN函數取末尾字符
    EXCEL函數公式大全之利用FIND函數和RIGHT函數LEN函數從字符串末尾取出指定長度的字符，從字符串中取出銷售員的姓名。第一步利用FIND函數找出部字符所在字符串中的位置。FIND函數的使用方法為：公式----插入函數----輸入函數名FIND函數----點擊轉到----點擊確定。我們會發現FIND函數有三個參數構成，第一個參數表示需要查找的文本或文本所在單元格。第二個參數表示包含要查找文本的字符串或字符串所在的單元格。
  • 如何利用COUNTIF函數,計算出滿足條件的單元格個數 - VBA語言專家
    今日給大家詳細講解的是用於計算滿足條件的單元格個數的COUNTIF函數，當然在之前的文章中也講解過，這裡是作為近期的統計函數專題來講解的。COUNTIF函數： 用來對搜索指定條件的單元格(即參數)的計數。
  • 如何從某一單元格快速提取人數——利用Excel字符與替換函數
    本文將用到LEN(一段文字/數字/符號) 結果為括號內字符個數LENB(一段文字/數字/符號) 結果是括號內字節個數SUBSTITUDE(一段文字比如，某項目的參與人名單寫在一個格子中。一旦統計人數，這時候就需要你幫他解決了。觀察數據表中志願者名單，姓名之間由頓號隔開，而最後一個人名後不加頓號，所以人數等於頓號的個數加上1。如何求得頓號的個數？思路：利用字符數之間的差求得頓號的個數。
  • Excel Substitute函數使用方法,含嵌套一次替換多個不同字符實例
    在 Excel 中，一共有兩個替換字符的函數，一個是Substitute函數，另一個是Replace函數；前者是用新字符替換舊字符，後者是用新字符替換指定字符數。Substitute函數既可以替換源文本中所有指定字符，又可以替換某個指定字符，並且還能嵌套使用以實現一次替換多個不同字符。
  • 用COUNT函數計算單元格個數,用COUNTBLANK函數計算空白單元格數
    一 COUNT函數函數定義：返回包含數字以及包含參數列表中的數字的單元格的個數. 函數COUNT在計數時，將把數字、空值、邏輯值、日期或以文字代表的數計算進去；但是錯誤值或其他無法轉化成數字的文字則被忽略。
  • excel中的西文字符和中文字符,以及提取這兩種字符的函數
    那如果在同一個單元格內，既有中文字符，又有西文字符，再分別用這兩個函數提取，會出現什麼情況呢？「不可不知ABCDE」的字符串，那麼結果是「9」，因為len函數識別的最小單位是1個中文字符，儘管「ABCDE」是西文字符，但是該函數仍然把它們算作一個完整的中文字符，所以該單元格內的字符數量是9。
  • 快速分離數據中的單位,怎麼能少了函數LEN和LENB的幫助?
    ）首先我們先來了解一下今天要介紹的新函數：函數LEN和函數LENB。函數LEN的基本語法形式是「=LEN（text）」，參數text在實例中通常就是某一單元格，len函數的功能就是返回目標單元格文本數據的字符長度。
  • 利用TRIM函數和SUBSTITUTE函數,靈活解決「空格」問題
    今天和大家分享一下去除單元格內空格的問題，這個問題其實是很頭疼的問題，因為空格在單元格中不明顯的顯示出來，很難判斷有無和個數的多少，但空格在EXCEL中卻佔用著字符的位置，給我們的順暢工作帶來麻煩。怎麼解決這個問題呢？
  • 最全批量去除EXCEL/WPS單元格中隱形符號和空格的方法
    在使用EXCEL的VLOOKUP、IF等函數過程中，有時因為單元格中存在看不見的空格或符號，導致函數應用出錯。為了解決出錯問題，我們需要去除看不見的空格和符號。下面是本人根據工作實踐總結的去除看不見的空格和符號的幾種方法，及各種方法的適用場合。
  • Excel統計某個字符在單元格文本中出現的次數的函數公式
    而在今天要學習，關於統計字符串在單元格文本中出現的次數，實際就是計算字符數，而計算字符數量的函數，非LEN函數莫屬。因此，下面作者君就舉例來說說如何使用LEN函數公式計算某字符出現的次數。LEN函數的表達式為：LEN（文本），它的結果會顯示為該文本的字符長度或字符數，比如LEN(1234)，它的結果為4，即字符數是4。這樣我們得知LEN(B2)的結果就是B2單元格內所有字符的總數量。
  • Excel公式與函數之美10:小而美的函數之LEN函數
    可以使用數組公式一次性求單元格區域中各單元格中的字符長度，如圖2所示。數字的個數取決於單元格B2中的字符數。也就是說，當LEN函數的參數單元格中的字符變化時，生成的數字數組也相應變化，這就為公式的擴展提供了靈活性。
  • Excel教程:四大經典案例帶你玩轉Substitute函數
    substitute函數有點類似於excel中的查找替換命令，但卻更靈活好用。substitute函數屬於文本查找類函數，就是查找某個字符，然後替換成別的字符。小雅將其翻譯成大白話，簡單的理解語法結構：Substitute（文本，舊文本，新文本，從第幾個開始替換）。
  • Excel中如何計算非空單元格個數,COUNTA函數了解一下~
    各位讀者朋友大家好，今天給大家介紹計數類函數的第二個基礎函數COUNTA函數，這個函數的作用是用來計算範圍內非空單元格的個數。這個函數的用法十分簡單，我們來看一下示例：我們可以看到，計算得出紅框中的非空單元格個數為3，因為F11單元格為空，所以未被計數。那我們來看一下這個函數的注意事項：COUNTA 函數計算包含任何類型的信息包括：空格（回車等非列印字符）、錯誤值、公式生成的空文本等。
  • EXCEL函數公式大全之利用SUBSTITUTE函數REPLACE函數刪除特定文本
    在前面的課程中我們學習過了從字符串的任意位置提取字符替換字符，今天我們要學習的是利用SUBSTITUTE函數和REPLACE函數的組合刪除字符串中的指定字符。第一步利用SUBSTITUTE函數將手機號中的字符「-」替換為空白。SUBSTITUTE函數的使用方式為：公式----插入函數----輸入函數名SUBSTITUTE函數----點擊轉到----點擊確定。
  • 【科研必備】Excel高級函數匯總——處理大數據必備(上)
    以上兩個軟體的計算功能也稱不上便捷。　　其實，excel就可以承擔起相當一部分計算、數據錄入、數據篩選，甚至是統計、作圖的功能。利用excel強大的「函數」功能，可以實現諸如計算兩個指定日期相差多少天、計數符合某條件的單元格有多少個、在複雜的病理報告中自動選出含有某個免疫組化陽性的蛋白、按特定需求查找單元格內容等任務。
  • Excel字符替換神器:Substitute函數的用法
    語法：substitute(text,old_text,new_text,[instance_num])翻釋成中文更好理解substitute(字符串，要被替換掉的字符,替換後的字符,[替換第幾個])在語法說明中，如果添加中括號，說明這人參數可以省略，所以Substitutue的第3個參數是可以省略的。
  • 數據分析常用的Excel函數合集(上)
    本文對數據分析需要用到的函數做了分類，並且有詳細的例子說明。Excel函數分類：關聯匹配類、清洗處理類、邏輯運算類、計算統計類、時間序列類由於篇幅過長，本篇先分享關聯匹配類和清洗處理類，其餘三個在明日推文第三條繼續分享。