Note: The other languages of the website are Google-translated. Back to English

Excel將文本和數字添加到單元格的指定位置

在 Excel 中,向單元格添加文本或數字是一項非常常見的工作。 例如在名稱之間添加空格,在單元格中添加前綴或後綴,在社交號碼中添加破折號。 在本教程中,它列出了 Excel 中幾乎所有的添加場景,並為您提供了相應的方法。

本教程的導航

1.添加到每個單元格的開頭

1.1 使用公式在單元格開頭添加

1.2 通過Flash Fill在單元格開頭添加

1.3 使用 VBA 代碼在單元格開頭添加

2.添加到每個單元格的末尾

2.1 使用公式在單元格末尾添加

2.2 通過 Flash Fill 在單元格末尾添加

2.3 使用 VBA 代碼在單元格末尾添加

3.添加到字符串中間

3.1 通過公式在字符串的第n個字符後添加

3.2 在字符串的第 n 個字符後添加 Flash Fill

3.3 使用 VBA 代碼在字符串的第 n 個字符後添加

4.使用Kutools for Excel添加文本(根據需要將字符添加到特定位置)

4.1 在第一個字符之前或最後一個字符之後添加文本

4.2 在指定位置添加文本

4.3 添加文本實用程序的擴展

5.在第一個特定文本之前或之後添加文本

5.1 在第一個特定文本之前添加文本

5.2 在第一個特定文本之後添加文本

6. 在每個單詞之前或之後添加字符

6.1 通過公式在每個單詞之前或之後添加字符

6.2 通過查找和替換在每個單詞之前或之後添加字符

6.3 通過 VBA 代碼在每個單詞之前或之後添加字符

7.在字符之間添加文字

7.1 使用自定義函數在每個字符/數字之間添加文本

7.2 用公式在每個數字之間添加空格

8. 在每個單詞之間添加字符

9.在文本和數字之間添加字符

9.1 用公式在文本和數字之間添加字符

9.2 使用 Flash Fill 在文本和數字之間添加字符

10.在電話號碼/社交號碼中添加破折號

10.1 用公式給數字添加破折號

10.2 按單元格格式為數字添加破折號

10.3 通過添加 Kutools for Excel 的文本將破折號添加到電話號碼

11.在單元格中添加前導零以固定長度

11.1 使用格式化單元格功能向單元格添加前導零

11.2 用公式向單元格添加前導零

12.給數字添加尾隨零以固定長度

13.在文本中添加尾隨空格

14.在文本或數字周圍添加引號/括號

14.1 在單元格格式周圍添加引號/括號

14.2 在公式周圍添加引號/括號

14.3 VBA加引號/括號

15.直接在公式結果中添加文本

備註

在本教程中,我創建了一些示例來解釋方法,您可以在使用下面的 VBA 代碼或公式時更改您需要的參考,或者您可以直接下載示例來嘗試方法。

doc樣本點擊下載示例文件


1.添加到每個單元格的開頭

這部分列出了在每個單元格的開頭添加字符的不同方法,如下圖所示:
在開始 1 處添加文檔

1.1 使用公式在單元格開頭添加


在這裡,您可以選擇以下三個公式之一:

Formula1 連接運算符“&”

用 & 字符“&”將多個文本連接在一起。

“文本”&單元格

Formula2 連接函數

CONCATENATE 函數用於將文本連接在一起。

連接(“文本”,單元格)

Formula3 CONCAT 函數

這是一項僅在 Excel 2019、Office 365 和 Excel 在線版中出現的新功能。

連接(“文本”,單元格)

在通用公式中:單元格是對要添加前綴的文本的單元格引用,文本是用於添加到單元格的文本。

使用上面的示例,您可以使用公式欄中的公式,如下所示:

“&”

=$E$3&B3 or ="蘋果"&B3

CONCATENATE功能

=連接($E$3,B3) or =CONCATENATE("Iphone",B3)

CONCAT功能

=CONCAT($E$3,B3) or =CONCAT("Iphone",B3)

媒體推介 Enter 鍵獲取結果,然後向下拖動自動填充手柄以將文本添加到範圍 B3:B6 的每個單元格。
在開始 1 處添加文檔

 注意: 文本應該用雙引號引起來,否則公式返回錯誤值#NAME?。 如果使用單元格引用文本,請記住使用絕對引用,可以按 F4 鍵將相對引用更改為絕對引用。

1.2 通過Flash Fill在單元格開頭添加


如果您在 Excel 2013 或更高版本中,強大的新功能, Flash填充, 可以根據您輸入的上述單元格自動填充單元格。

選擇第一個原始數據旁邊的單元格,手動鍵入帶有前綴文本的第一個數據,如下所示:
在開始 1 處添加文檔

然後在下面的單元格中,繼續輸入帶有前綴文本的第二個數據,在輸入過程中會顯示一個灰色的列表,按 Enter 鍵允許 Flash Fill 自動填充數據。
在開始 1 處添加文檔在開始 1 處添加文檔

如果預覽列表未生成,請轉到主頁選項卡,然後單擊 > Flash填充 手動運行它。

1.3 使用 VBA 代碼在單元格開頭添加


如果您熟悉 VBA 代碼,這裡介紹一個 VBA 代碼,用於在區域中每個單元格的開頭添加文本。

1. 選擇要添加前綴文本的單元格範圍,然後按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.然後在彈出的窗口中,單擊 插入 > 模塊 插入一個空白的新模塊。
在開始 1 處添加文檔

3. 將下面的 VBA 代碼複製並粘貼到新模塊中。

VBA:在單元格的開頭添加

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub
 注意:在代碼腳本中, iphone 是您要在所選單元格開頭添加的文本,請根據需要進行更改。

在開始 1 處添加文檔

4。 按 F5 鍵或單擊 按鍵 在開始 1 處添加文檔 在窗口中激活 VBA 代碼。

現在所有選定的單元格都在開頭添加了文本“Iphone”。
在開始 1 處添加文檔


2.添加到每個單元格的末尾

除了在單元格開頭添加文本外,在單元格末尾添加文本也很常見。 在這裡,我們以在每個單元格末尾添加單元為例,如下圖所示:
文檔在末尾添加 1

2.1 使用公式在單元格末尾添加


在這裡,您可以選擇以下三個公式之一:

Formula1 連接運算符“&”

用 & 字符“&”將多個文本連接在一起。

單元格&“文本”

Formula2 連接函數

CONCATENATE 函數用於將文本連接在一起。

連接(單元格,“文本”)

Formula3 CONCAT 函數

這是一項僅在 Excel 2019、Office 365 和 Excel 在線版中出現的新功能。

連接(單元格,“文本”)

在通用公式中:單元格是對要添加後綴的文本的單元格引用,文本是用於添加到單元格的文本。

使用上面的示例,您可以使用公式欄中的公式,如下所示:

“&”

=B3&$E$3 or =B3&"公斤"

CONCATENATE功能

=連接(B3,$E$3) or =連接(B3,“公斤”)

CONCAT功能

=CONCAT(B3, $E$3) or =CONCAT(B3,“公斤”)

媒體推介 Enter 鍵獲取結果,然後向下拖動自動填充句柄以將文本添加到範圍 B3:B6 的每個單元格的末尾。
文檔在末尾添加 1

 注意: 文本應該用雙引號引起來,否則公式返回錯誤值#NAME?。 如果使用單元格引用文本,請記住使用絕對引用,可以按 F4 鍵將相對引用更改為絕對引用。

2.2 通過 Flash Fill 在單元格末尾添加


如果您在 Excel 2013 或更高版本中,強大的新功能, Flash填充, 可以根據您輸入的上述單元格自動填充單元格。

選擇第一個原始數據旁邊的單元格,手動鍵入帶有後綴文本的第一個數據,如下所示:
文檔在末尾添加 1

然後激活第一個結果下的單元格,點擊 數據 > Flash填充,包括活動單元格在內的所有下面的單元格將被自動填充。
文檔在末尾添加 1
文檔在末尾添加 1

2.3 使用 VBA 代碼在單元格末尾添加


這是一個 VBA 代碼,可以在範圍內每個單元格的末尾添加文本。

1. 選擇要添加後綴文本的單元格範圍,然後按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.然後在彈出的窗口中,單擊 插入 > 模塊 插入一個空白的新模塊。
在開始 1 處添加文檔

3. 將下面的 VBA 代碼複製並粘貼到新模塊中。

VBA:在單元格末尾添加

Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub
注意:在代碼腳本中, Kg 是您要在所選單元格末尾添加的文本,請根據需要進行更改。 

在開始 1 處添加文檔

4。 按 F5 鍵或單擊 按鍵 在開始 1 處添加文檔 在窗口中激活 VBA 代碼。

現在文本“Kg”已添加到每個單元格的末尾。
在開始 1 處添加文檔


3.添加到字符串中間

對於大多數人來說,在單元格的開頭或結尾添加文本很容易,但在單元格的中間添加文本可能有點困難。 例如,如下圖所示,在 B3:B7 範圍內每個單元格的第二個字符的末尾添加一個分隔符,例如冒號:。
文檔添加到中間 1

3.1 通過公式在字符串的第n個字符後添加


這裡有四個公式供您處理這項工作,選擇一個使用:

Formula1 結合 LEFT 和 RIGHT 函數

左(單元格,n)&“文本”&右(單元格,LEN(單元格)-n)

Formula2 COMBINE CONCATENATE(CONCAT)、RIGHT 和 LEN 函數

連接(左(單元格,n),“文本”,右(單元格,LEN(單元格)-n))

Or

連接(左(單元格,n),“文本”,右(單元格,LEN(單元格)-n))

Formula3 替換功能

替換(單元格,n+1,0,“文本”)

在通用公式中:cell 是對要在中間添加文本的文本的單元格引用,text 是用於添加到單元格的文本,n 是定義在文本字符串中的哪個字符之後要添加的數字添加文字。

為了解決上述工作,請使用以下公式:

結合 LEFT 和 RIGHT 功能

=左(B3,2)&“:”&右(B3,LEN(B3)-2) or =左(B3, 2) & $E$3 & 右(B3, LEN(B3) -2)

組合連接(CONCAT)、RIGHT 和 LEN 函數

=連接(左(B3,2),“:”,右(B3,LEN(B3)-2)) or =CONCAT(左(B3,2),“:”,右(B3,LEN(B3)-2))

更換功能

=替換(B3, 2+1, 0, ":") or =替換(B3, 2+1, 0, $E$3)

媒體推介 Enter 鍵獲得結果,然後向下拖動自動填充手柄以在範圍 B3:B7 的每個單元格的第二個字符之後添加文本。
文檔添加到中間 1

 注意: 文本應該用雙引號引起來,否則公式返回錯誤值#NAME?。 如果使用單元格引用文本,請記住使用絕對引用,可以按 F4 鍵將相對引用更改為絕對引用。

3.2 在字符串的第 n 個字符後添加 Flash Fill


如果您使用的是 Excel 2013 或更高版本,則 Flash填充, 可以根據您輸入的上述單元格自動填充單元格。

選擇第一個原始數據旁邊的單元格,手動鍵入第一個數據,在第二個字符後加一個冒號,如下所示:
文檔添加到中間 1

然後激活第一個結果下的單元格,按 按Ctrl + E 激活鍵 Flash填充 命令,包括活動單元格在內的所有下面的單元格都已自動填充。
文檔添加到中間 1

注意:如果您在 MAC 系統中,請按 Command + E 鍵。 

3.3 使用 VBA 代碼在字符串的第 n 個字符後添加


VBA 代碼也可以完成這項工作。

1。 按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.然後在彈出的窗口中,單擊 插入 > 模塊 插入一個空白的新模塊。
在開始 1 處添加文檔

3. 將下面的 VBA 代碼複製並粘貼到新模塊中。

VBA:添加到單元格中間

Sub AddToMidduleOfString()
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    For Each Rng In WorkRng
        Rng.Value = VBA.Left(Rng.Value, 2 & ":" & VBA.Mid(Rng.Value, 3, VBA.Len(Rng.Value) - 1)
    Next
End Sub
注意:在代碼腳本中, 2 是數字定義要在其後添加文本的位置, : 是您要添加到的文本, 3 是 2 和您添加的文本長度的總和。 再舉一個例子,要在字符串“AB”的第一個字符後添加“and”,腳本應該是 Rng.Value = VBA.Left(Rng.Value, 1) & "" & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Value) - 1) 

在開始 1 處添加文檔

4。 按 F5 鍵或單擊 按鍵 在開始 1 處添加文檔 在窗口中激活 VBA 代碼。 彈出一個對話框,用於選擇要添加文本的單元格。
在開始 1 處添加文檔

5。 點擊 OK. 現在文本“:”已添加到 B19:B23 範圍內每個單元格的第二個字符之後。
在開始 1 處添加文檔


4.使用Kutools for Excel添加文本(根據需要將字符添加到特定位置)

如果你厭倦了公式和 VBA,你可以嘗試一個強大而方便的工具, Kutools for Excel,它可以幫助你 添加文本 功能。
doc添加文本1

此添加文本功能可以:

  • 在第一個字符之前添加文本
  • 在最後一個字符後添加文本
  • 在指定位置添加文本
  • 延期
    在大寫字母前添加文本
    在小寫字母前添加文本
    在大寫/小寫字母之前添加文本
    在數字字符前添加文本

點擊立即獲得 30 天免費試用。

選擇要添加文本的單元格,然後通過單擊應用添加文本 庫工具 > 文本 > 添加文本.
doc添加文本1
4.1 在第一個字符之前或最後一個字符之後添加文本


添加文本 對話框中,鍵入要添加到單元格中的文本 文本 文本框,然後檢查 在第一個字符之前位置 部分。
doc添加文本1

點擊 登記 or Ok 完成添加。
doc添加文本1

要在最後一個字符之後添加文本,請鍵入要添加到單元格中的文本 文本 文本框,然後檢查 最後一個字符之後位置 部分。
doc添加文本1

點擊 登記 or Ok 完成添加。
doc添加文本1

4.2 在指定位置添加文本


有時,您可能希望將文本添加到單元格的中間,或者將文本添加到單元格的多個位置。 假設在社交號碼的第三和第七位之後添加分隔符“-”,如下圖所示:
doc添加文本1

添加文本 對話框,將文本輸入 文本 文本框,然後檢查 指定 選項,然後使用逗號將位置鍵入到文本框中,然後單擊 Ok or 登記.
doc添加文本1

4.3 添加文本實用程序的擴展


除了將文本添加到正常位置(開始、結束和指定位置)之外, 添加文本 實用程序還支持添加文本

  • 大寫字母之前
  • 小寫字母前
  • 在大寫/小寫字母之前
  • 在數字字符之前

添加文本 對話框,將文本輸入 文本 文本框,然後檢查 僅添加到 選項,然後單擊下面的下拉菜單,根據需要選擇操作。
doc添加文本1
doc添加文本1

除了添加文本功能,Kutools for Excel 還有其他 300 多個有用的功能,可以在 Excel 的不同工作中幫到您,現在就免費下載。


5.在第一個特定文本之前或之後添加文本

這部分提供了在 Excel 中首次出現的特定文本之前或之後添加文本的公式。

5.1 在第一個特定文本之前添加文本


例如,您想在單元格中的第一個文本“價格”之前添加文本“(銷售)”,如下圖所示:
doc添加文本1

在 Excel 中,REPLACE 和 SUBSTITUTE 函數可以解決這個問題。

更換功能

替換(單元格,查找(“查找文本”,單元格),0,“添加文本”)

替代功能

替代(單元格,“查找文本”,“替換文本”,1)

在這種情況下,請使用上述公式:

=REPLACE(B4,FIND("價格",B4),0,"(銷售)")

Or

=SUBSTITUTE(B4,"價格","(銷售)價格",1)

在這種情況下,公式參數是

單元格:B4,

find_text:價格,

Add_text:(銷售),

Replace_text:(銷售)價格。

媒體推介 Enter 鍵獲得第一個添加結果,然後將自動填充句柄拖到需要此公式的單元格上。
doc添加文本1

5.2 在第一個特定文本之後添加文本


例如,您想在單元格中的第一個冒號“:”之後添加貨幣符號“$”,如下圖所示:
doc添加文本1

在 Excel 中,REPLACE 和 SUBSTITUTE 函數可以解決這個問題。

更換功能

替換(單元格,查找(“查找文本”,單元格),查找文本長度,“替換文本”)

替代功能

替代(單元格,“查找文本”,“替換文本”)

在這種情況下,請使用上述公式:

=替換(B12,查找(“:”,B12),1,“:$”)

Or

=SUBSTITUTE(B12,":",":$")

在這種情況下,公式參數是

單元格:B12,

查找文本: :,

替換文本::$。

查找文本長度:1

媒體推介 Enter 鍵獲得第一個添加結果,然後將自動填充句柄拖到需要此公式的單元格上。
doc添加文本1


6. 在每個單詞之前或之後添加字符

在這部分教程中,它介紹了三種不同的方法(公式、查找和替換、VBA)來幫助您在單元格的每個單詞之前或之後添加字符。

在每個單詞前添加字符 
doc 在每個單詞前添加 1
在每個單詞後添加字符
doc 在每個單詞前添加 1

6.1 通過公式在每個單詞之前或之後添加字符


可以使用 SUBSTITUTE 函數來解決這個問題,一般公式如下:

在每個單詞之前添加

"字符"&SUBSTITUTE(字符串, " "," 字符")

在每個單詞後添加

SUBSTITUTE(字符串," ","字符")&"字符"

假設在 B3:B6 列表中的每個單詞前添加“+”,請使用以下公式:

="+"&SUBSTITUTE(B3, " "," +")

媒體推介 Enter 鍵獲取第一個結果,然後向下拖動自動填充手柄以獲取所有結果。
doc 在每個單詞前添加 1

假設在列表 B11:B14 的每個單詞後添加 ($),請使用以下公式:

=SUBSTITUTE(B11," ","($) ")&"($)"

媒體推介 Enter 鍵獲取第一個結果,然後向下拖動自動填充手柄以獲取所有結果。
doc 在每個單詞前添加 1

6.2 通過查找和替換在每個單詞之前或之後添加字符


如果您想在每個單詞之前添加字符(不包括第一個),或者在每個單詞之後添加字符(不包括最後一個),如下圖所示,Excel 內置 查找和替換 實用程序可以處理這項工作。

在除第一個單詞之外的每個單詞之前添加
doc 在每個單詞前添加 1

在除最後一個單詞之外的每個單詞之後添加
doc 在每個單詞前添加 1

1. 選擇要在每個單詞之前或之後添加字符的單元格,然後按 按Ctrl + H 啟用 查找和替換 對話。

2. 輸入空格 查找內容 文本框,然後輸入帶有空格的新字符 更換 文本框,最後點擊 “全部替換” 按鈕。

手術 一般
在每個單詞前添加字符 在空格後添加新字符 在每個單詞之前添加(銷售)

doc 在每個單詞前添加 1doc 在每個單詞前添加 1

手術 一般
在每個單詞後添加字符 新字符後跟一個空格 在每個單詞之後添加 -

doc 在每個單詞前添加 1doc 在每個單詞前添加 1

6.3 通過 VBA 代碼在每個單詞之前或之後添加字符

這裡提供了兩個 VBA 代碼,用於在每個單詞之前或之後添加字符,請按照以下步驟運行代碼。

1。 按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.在彈出的窗口中,單擊 插入 > 模塊 插入一個新的空白模塊。
doc 在每個單詞前添加 1

3. 將下面的 VBA 代碼複製並粘貼到模塊中。

在每個單詞之前添加

Sub InsertCharBeforeWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
    On Error Resume Next
   
    Set xSRg = Application.Selection
    Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
    If StrPtr(xInStr) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xArr = Split(xCell.Text, " ")
        xValue = ""
        For Each xStr In xArr
            If Trim(xStr) <> "" Then
                If xValue = "" Then
                    xValue = xInStr & Trim(xStr)
                Else
                    xValue = xValue & " " & xInStr & Trim(xStr)
                    End If
            End If
        Next
        xCell.Value = xValue
    Next
    Application.ScreenUpdating = True
End Sub

在每個單詞後添加

Sub InsertCharAfterWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
    On Error Resume Next
 
    Set xSRg = Application.Selection
    Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
   
    xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
    If StrPtr(xInStr) = 0 Then Exit Sub
   
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xArr = Split(xCell.Text, " ")
        xValue = ""
        For Each xStr In xArr
            If Trim(xStr) <> "" Then
                If xValue = "" Then
                    xValue = Trim(xStr) & xInStr
                Else
                    xValue = xValue & " " & Trim(xStr) & xInStr
                End If
            End If
        Next
        xCell.Value = xValue
    Next
    Application.ScreenUpdating = True
End Sub

4。 按 F5 運行代碼的鍵(或者您可以單擊 按鍵  在開始 1 處添加文檔 ),彈出一個對話框,要求您選擇連續的單元格來工作,單擊 OK.
doc 在每個單詞前添加 1

5. 在第二個彈出對話框中,輸入要添加的字符,單擊 OK.
doc 在每個單詞前添加 1

然後字符將被添加到每個單詞的前面或末尾。


7.在字符之間添加文字

有時,您可能希望在每個字符之間添加文本。 在這部分,它列出了兩種場景,一種是在每個字符之間添加文本,另一種是在每個數字之間添加空格。

7.1 使用自定義函數在每個字符/數字之間添加文本


要在字符串中的每個字符之間添加字符,用戶定義函數可以幫助您。

在這種情況下,我們在每個字符之間添加一個空格。

1。 按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.在彈出的窗口中,單擊 插入 > 模塊 插入一個新的空白模塊。
doc 在每個單詞前添加 1

3. 將下面的 VBA 代碼複製並粘貼到模塊中。

在字符之間添加文本

Function AddText(Str As String) As String
    Dim i As Long
    For i = 1 To Len(Str)
        AddText = AddText & Mid(Str, i, 1) & " "
    Next i
    AddSpace = Trim(AddSpace)
End Function

4. 保存代碼並關閉窗口以返回工作表,然後在要放置添加結果的單元格中鍵入以下公式。

=添加文本(B3)

B3 是包含要在字符之間添加空格的字符串的單元格。

5。 按 Enter 獲得結果的關鍵,將自動填充手柄拖到單元格上以獲得您需要的其他結果。
doc 在每個單詞前添加 1

 

注意:

1) 在 VBA 代碼中,您可以更改 “” 根據需要更改為其他文本,假設將其更改為 “ - ” 在字符之間添加 -。

2) 代碼將在每個字符後添加新文本。 如果不需要,可以使用以下公式刪除最後添加的文本。 有關公式的更多詳細信息,請訪問:

如何從 Exc 中的單元格或字符串中刪除第一個或最後 N 個字符埃爾?

左(單元格,LEN(單元格)-text_length)

7.2 用公式在每個數字之間添加空格


如果要在數字字符串的數字之間添加空格,可以使用 TEXT 函數。

注意: 要在其間添加空格的範圍數需要保持相同的長度,否則可能會導致某些結果不正確。 

假設這裡是 B8:B10 範圍內的 12 位數字列表,請選擇一個將放置結果的單元格,鍵入以下公式:

=TEXT(B10,"# # # # # # # #")

媒體推介 Enter 鍵,然後將自動填充手柄拖到其他單元格上。
doc 在每個單詞前添加 1

如果數字的長度為 5,則公式將更改為 =TEXT(B10,"# # # # #").


8. 在每個單詞之間添加字符

如果您想在每個單詞之間添加字符,假設在名字、中間名、姓氏之間添加空格,如下圖所示,您可以使用用戶定義函數。
doc 在每個單詞前添加 1

1。 按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.在彈出的窗口中,單擊 插入 > 模塊 插入一個新的空白模塊。

3. 將下面的 VBA 代碼複製並粘貼到模塊中。

在單詞之間添加字符

Function AddCharacters(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddCharacters = xOut
End Function

4. 保存代碼並關閉窗口以返回工作表,然後在要放置添加結果的單元格中鍵入以下公式。

=添加字符(B3)

B3 是包含要在單詞之間添加空格的字符串的單元格。

5。 按 Enter 獲得結果的關鍵,將自動填充手柄拖到單元格上以獲得您需要的其他結果。
doc 在每個單詞前添加 1

 注意:在 VBA 代碼中,您可以更改 “” 根據需要更改為其他文本,假設將其更改為 “ - ” 在單詞之間添加-。

9.在文本和數字之間添加字符

要在文本和數字之間添加特定字符,如下圖所示,這裡提供了兩種方法。
在文本和數字 1 之間添加文檔

9.1 用公式在文本和數字之間添加字符


如果文本在數字前面,則通用公式為

TRIM(REPLACE(string,MIN(FIND({1,2,3,4,5,6,7,8,9,0},string&"1234567890")),0,"-"))

以上述情況為例,請使用以下公式:

=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))

媒體推介 Enter 鍵獲得第一個結果,然後將自動填充手柄拖到其他單元格上以填充此公式。
在文本和數字 1 之間添加文檔

如果數字在文本的前面,則通用公式為

LEFT(string,SUM(LEN(string)-LEN(SUBSTITUTE(string,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT (string,LEN(B13)- SUM(LEN(string)-LEN(SUBSTITUTE(string, {0,1,2,3,4,5,6,7,8,9}, ""))))

以上述情況為例,請使用以下公式:

=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(B13,LEN(B13)- SUM(LEN(B13)-LEN(SUBSTITUTE(B13, {0,1,2,3,4,5,6,7,8,9}, ""))))

媒體推介 Enter 鍵獲得第一個結果,然後將自動填充手柄拖到其他單元格上以填充此公式。
在文本和數字 1 之間添加文檔

9.2 使用 Flash Fill 在文本和數字之間添加字符


如果您使用的是 Excel 2013 或更高版本,則 Flash填充 還可以幫助您在文本和數字之間添加字符。

在原始數據旁邊,鍵入已在文本和數字之間添加字符的新數據。 然後按 Enter 鍵轉到第一個結果的下一個單元格。
在文本和數字 1 之間添加文檔

然後按 按Ctrl + E 鍵啟用快速填充。
在文本和數字 1 之間添加文檔

延期

本教程還列出了一些我們在日常生活或工作中可能遇到的添加文本的場景。


10.在電話號碼/社交號碼中添加破折號

有時,您需要在一系列電話號碼或社交號碼中添加分隔符,例如破折號,如下圖所示。 如果需要添加數百個數字,將嘗試手動輸入。 在這一部分中,它介紹了三種快速處理這項工作的技巧。
doc將破折號添加到數字1

10.1 用公式給數字添加破折號


這裡有兩個公式可以快速將破折號添加到數字中。 要在單元格 B3 中的第三個和第六個數字後添加破折號,請使用以下公式:

更換功能

=替換(替換(B3,4,0,"-"),8,0,"-")

TEXT功能

=TEXT(B3,"???-???-???")

媒體推介 Enter 鍵獲得第一個結果,然後將自動填充手柄拖到其他單元格上。
doc將破折號添加到數字1

注意:

您可以根據需要更改上述公式的參數。 假設添加 “ - ” 在第二、第四和第六位數字之後,您可以使用以下公式:

=替換(替換(替換(B3,3,0,"-"),6,0,"-"),9,0,"-")

=TEXT(B3,"??-??-??-???")

 

10.2 按單元格格式為數字添加破折號


要直接將破折號添加到原始單元格中的數字,您可以使用 單元格格式 功能。

1. 選擇要添加破折號的號碼,然後按 按Ctrl + 1 啟用 單元格格式 對話。

2。 在裡面 單元格格式 對話框,在 標籤,選擇 習俗 來自 類別 部分。

3.然後在右邊 類別 部分,類型 ###-###-### 在文本框中,單擊 OK.
doc將破折號添加到數字1

現在所選數字已添加破折號。
doc將破折號添加到數字1

10.3 通過添加 Kutools for Excel 的文本將破折號添加到電話號碼


如果你有 Kutools for Excel 安裝在 Excel 中, 添加文本 功能也可以幫您一個忙。

1.選擇包含需要添加破折號的數字的單元格,單擊 庫工具 > 文本 > 添加文字。
doc將破折號添加到數字1

2。 在裡面 添加文本 對話框中,輸入破折號或其他分隔符 文本 文本框,選擇 指定 選項,然後在下面的文本框中鍵入要添加破折號的位置並用逗號分隔它們。
doc將破折號添加到數字1

3。 點擊 Ok 或申請。 然後所選數字已添加破折號。

免費下載 Add Text 30 天試用版。


11.在單元格中添加前導零以固定長度

假設有一個不同長度的數字列表,您想要添加前導零以使它們具有相同的長度並且看起來整潔,如下面的屏幕截圖所示。 這裡提供了兩種處理工作的方法。
doc添加前導零1

11.1 使用格式化單元格功能向單元格添加前導零


您可以使用 單元格格式 設置自定義格式以通過添加前導零來固定單元格長度的功能。

1. 選擇要添加前導零的單元格,然後按 按Ctrl + 1 啟用鍵 單元格格式 對話。

2。 在裡面 單元格格式 對話框,在 標籤,選擇 習俗 來自 類別 部分。

3.然後在右邊 類別 部分,類型 0000 進入文本框(要將數字長度固定為 4 位,輸入 0000,您可以將其更改為其他長度,例如 5 位,輸入 00000),單擊 OK.
doc添加前導零1

然後數字已添加前導零並具有相同的長度。
doc添加前導零1

注意:如果原始數據的長度超過設定的長度,則顯示原始數據,不帶前導零。


11.2 用公式向單元格添加前導零


如果您不想更改原始數據,可以使用公式將前導零添加到其他位置的數字。

這裡有三個功能可以幫助你。

Formula1 TEXT 函數

文本(數字,“00…”)

Formula2 RIGHT 函數

RIGHT("00…"&數字,長度)

Formula3 BASE 函數

BASE(數字,10,長度)

公式中零的數量等於數字的長度。

此處設置 4 位為數字長度,請使用以下公式:

= TEXT(B10,“ 0000”)

=RIGHT("0000"&B10,4)

=基礎(B10,10,4)

媒體推介 Enter 鍵並向下拖動自動填充手柄。
doc添加前導零1

注意:如果原始數據的長度超過設定的長度,則顯示原始數據,不帶前導零。


12.給數字添加尾隨零以固定長度

如果您想在數字中添加尾隨零以製作固定長度,假設在單元格 B3:B7 中的數字中添加尾隨零以使其具有 5 位長度,如下圖所示,您可以應用公式來處理工作.
doc 添加尾隨零 1

REPT 功能

number&REPT("0",length-LEN(number))

在公式中,參數“number”表示要添加尾隨零的數字或單元格引用,參數“length”是您想要的固定長度。

請使用以下公式:

=B3&REPT("0",5-LEN(B3))

媒體推介 Enter 鍵並將自動填充句柄向下拖動到需要此公式的單元格。
doc 添加尾隨零 1


13.在文本中添加尾隨空格

如果要將工作表中的數據導出或複製到記事本,由於單元格值的長度不同,文本可能放置不整齊,為了使它們看起來整潔,如下圖所示,您需要添加尾隨空格。
doc添加尾隨空格1

為了解決這項工作,REPT 功能可以幫您一個忙。

左(文本和重複(“”,最大長度),最大長度)

在公式中,參數“text”是要添加尾隨空格的文本或單元格引用,參數“max_length”是要製作文本的固定長度,必須大於或等於使用文本的最大長度。

要為單元格 B3:C6 中的文本添加尾隨空格,請使用以下公式:

=LEFT(B3 & REPT(" ",10),10)

媒體推介 Enter 鍵,然後將自動填充句柄拖到需要此公式的所有單元格上。

在這種情況下,由於使用的文本中最長的是 9 個字符,因此我們在這裡使用 10。
doc添加尾隨空格1


14.在文本或數字周圍添加引號/括號

有時,您需要在單元格中的文本或數字周圍添加引號或括號,如下圖所示,除了手動輸入,這裡有兩種 Excel 中的方法可以快速解決它。
doc 加引號 1

14.1 在單元格格式周圍添加引號/括號


如果要在原始數據中的文本周圍添加引號,Excel 中的“設置單元格格式”功能可以為您提供幫助。

1.選擇要添加引號的單元格,按 按Ctrl + 1 啟用 單元格格式 對話。

2。 在裡面 單元格格式 對話框下 標籤,選擇 習俗 從清單中 類別,然後轉到右側部分鍵入 “”“ @”“”類別 文本框。 請點擊 OK.
doc 加引號 1

所選單元格中的文本已添加引號。

如果要在文本周圍添加括號,請在“設置單元格格式”對話框的“類型”文本框中,使用 (@).

14.2 在公式周圍添加引號/括號


公式也可用於為其他單元格中的文本添加引號。

使用 & 連接器

““““&文本&””””

Or

字符(34)&文本&字符(34)

在這種情況下,單元格 B11 中的文本,請使用以下公式:

=""""&B11&""""

Or

=char(34)&B11&char(34)

媒體推介 Enter 鍵並向下拖動自動填充手柄以填充單元格。
doc 加引號 1

如果要在文本周圍添加括號,請使用公式作為 “(“&文本&”)”.

14.3 VBA加引號/括號


如果你需要 VBA 代碼來解決這個工作,這裡有一個 VBA。

1。 按 其他 + F11 啟用 Microsoft Visual Basic for Applications 窗口。

2.然後在彈出的窗口中,單擊 插入 > 模塊 插入一個空白的新模塊。

3. 將下面的 VBA 代碼複製並粘貼到新模塊中。

VBA:在文本周圍添加引號

Sub addquotationmarksorbrackets()
'UpdatebyExtendOffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = """" & Rng.Value & """"
Next
End Sub
 注意:在代碼腳本中, """" & Rng.Value & """" 表示在文本周圍加“”,如果要在文本周圍加括號(),則改為 "(" & Rng.Value & ")".

doc 加引號 1

4。 按 F5 鍵或單擊 按鍵 doc 加引號 1 在窗口中激活 VBA 代碼。 彈出一個對話框,用於選擇單元格以添加引號。
doc 加引號 1

5。 點擊 OK. 現在已在所選單元格中的文本周圍添加了引號。


15.直接在公式結果中添加文本

有時,為了幫助其他用戶更好地理解數據,您可能需要在公式結果中添加文本,如下圖所示。 要在公式中包含文本,您可以在文本周圍使用引號並添加 & 連接符以將文本和公式組合在一起。
doc在公式1中添加文本

案例 1 在公式前添加文本

"今天是 "&TEXT(TODAY(),"dddd, mmmm dd.")
doc在公式1中添加文本

案例2後添加文字

TEXT(NOW(),"HH:MM:SS")&" 是當前時間。"
doc在公式1中添加文本

案例3 在公式的兩側添加文本

“今天賣出了“&data&”公斤。”
doc在公式1中添加文本


更多 Excel 教程:

將多個工作簿/工作表合併為一個
本教程,列出了您可能遇到的幾乎所有組合場景,並為您提供了相關專業的解決方案。

拆分文本、數字和日期單元格(分成多列)
本教程分為三個部分:拆分文本單元格、拆分數字單元格和拆分日期單元格。 每個部分都提供了不同的示例,以幫助您了解在遇到相同問題時如何處理拆分作業。

在Excel中合併多個單元格的內容而不會丟失數據
本教程將提取範圍縮小到單元格中的特定位置,並收集了不同的方法來幫助在 Excel 中按特定位置從單元格中提取文本或數字。

在 Excel 中比較兩列的匹配和差異
本文涵蓋了您可能遇到的比較兩列的大多數可能場景,希望對您有所幫助。



  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據; 拆分單元格內容; 合併重複的行和總和/平均值...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 收藏并快速插入公式,範圍,圖表和圖片; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 數據透視表分組依據 週號,週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 每天將您的工作效率提高50%,並減少數百次鼠標單擊!
officetab底部
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0  字符
推薦地點