提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中為單元格添加前綴或後綴?

在某些情況下,有時您需要在所有單元格的開頭或結尾添加常用文本。 當然,您可以將每個單元格的前綴或後綴逐個添加。 但是,當有很多單元時,工作會變得艱難和耗時。 本文將向您展示一些關於如何輕鬆地在Excel中為選定單元格添加前綴或後綴的提示。

將公式前綴或後綴添加到所有單元格

使用VBA為所有單元添加前綴或後綴

使用Kutools for Excel為所有單元格添加前綴或後綴

從選中的所有單元格中刪除前綴或後綴

輕鬆地將常用文本添加到Excel中所有單元格的開頭或結尾(前綴或後綴)

使用“自動填充”功能可以輕鬆填充列中具有相同內容的所有單元格。 但是,如何將相同的前綴或後綴添加到列中的所有單元格? 比較分別為每個單元格鍵入前綴或後綴, Kutools for ExcelAdd Text 實用程序提供了一個簡單的解決方法,只需點擊幾下即可完成。 全功能免費試用30天!
廣告添加文字前綴後綴

Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文字; 將數字和貨幣轉換為英文單詞。
  • 合併工具:將多個工作簿和工作表合二為一; 合併多個單元格/行/列,而不會丟失數據; 合併重複的行和總和。
  • 拆分工具:根據價值將數據分割成多個工作表; 一本工作簿可轉換為多個Excel,PDF或CSV文件; 一列到多列。
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 將個性化電子郵件批量發送給多個收件人。
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

箭頭藍色右泡 將公式前綴或後綴添加到所有單元格

Excel的 串聯 功能可以快速為單個單元插入前綴或後綴。

1。 輸入功能 =CONCATENATE("Food - ",A1) 在一個空白的細胞中,Cell說 C1,然後拖動此單元格的自動填充句柄到要填充的範圍。 並且所有的單元格都添加了特定的前綴文本。 見截圖: 看截圖:

您可以使用concatenate函數插入前綴,後綴或後綴和前綴,如下所示:

輸入公式 結果在細胞中
= Concatenate ("Food - ", A1) 食物 - 蘋果
=Concatenate (A1, " - Fruit") 蘋果 - 水果
=Concatenate ("Food - ", A1, " - Fruit") 食物 - 蘋果 - 水果

箭頭藍色右泡使用VBA為所有單元添加前綴或後綴

你也可以用下面的VBA代碼來處理這個問題:

1。 選擇您想要插入前綴或後綴的範圍。

2。 點擊 Developer > Visual Basic,和一個新的 Microsoft Visual Basic for applications 窗口會顯示,點擊 Insert > Module,然後輸入以下代碼:

VBA:為文本添加前綴:

Sub AddTextOnLeft()
'Updateby20131128
Dim Rng As Range
Dim WorkRng As Range
Dim addStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
addStr = Application.InputBox("Add text", xTitleId, "", Type:=2)
For Each Rng In WorkRng
    Rng.Value = addStr & Rng.Value
Next
End Sub

VBA:為文本添加後綴:

Sub AddTextOnRight()
'Updateby20131128
Dim Rng As Range
Dim WorkRng As Range
Dim addStr As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
addStr = Application.InputBox("Add text", xTitleId, "", Type:=2)
For Each Rng In WorkRng
    Rng.Value = Rng.Value & addStr
Next
End Sub

3。 然後點擊 Run 按鈕或按下 F5 鍵來運行VBA。

4。 現在請指定您要為第一個添加前綴或後綴的範圍 KutoolsforExcel 對話框並單擊 OK 按鈕,然後鍵入要添加到第二個的後綴或前綴 KutoolsforExcel 對話框並單擊 OK 按鈕。 看下面的截圖:

現在,指定的後綴或前綴已添加到每個選定單元格中,如下面的屏幕截圖所示:


箭頭藍色右泡 使用Kutools for Excel為所有單元格添加前綴或後綴

您可能不熟悉Excel中的函數和VBA代碼。 和這個 Add Text 實用程序 Kutools for Excel 將幫助您快速將前綴或後綴插入到任何選定的範圍。

1。 選擇要添加前綴或後綴的單元格,然後單擊 Kutools > Text > Add Text,看截圖:

2。 在 Add Text 對話框中輸入您的前綴或後綴 文本 框,檢查 Before first character 選項 (用於添加前綴)或 After last character 選項 (用於添加後綴),然後點擊 Ok 按鈕。
現在,指定的前綴或後綴一次添加到每個選定的單元格中。 看截圖:


箭頭藍色右泡演示:使用Kutools for Excel為多個單元格添加前綴或後綴

Kutools for Excel 包含多個用於Excel的300便捷工具,可以在30天內不受限制地免費試用。 立即下載並免費試用!

在Excel中從單元格的開始/結束/刪除中刪除前綴或後綴(相同數量的字符)

Kutools for Excel's Remove By Position 功能使您可以從單元格中的文本字符串的開始/結束/指定位置刪除前綴或後綴(特定數量的字符)。 全功能免費試用30天!
ad刪除前綴後綴

箭頭藍色右泡相關文章:


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Pboro · 1 years ago
    How do I prefix a letter to a date in the format "Cmmddyy".

    Example. A1 contains date in format mm/dd/yy (let's say 01/31/18)

    I want A2 to show "C013118".
    • To post as a guest, your comment is unpublished.
      Vasudeva · 2 months ago
      Use this formula in A2:
      =IF(A1<>"","C"&IF(MONTH(A1)>=10,MONTH(A1),"0"&MONTH(A1))&DAY(A1)&RIGHT(YEAR(A1),2),"")
  • To post as a guest, your comment is unpublished.
    daniyal ejaz · 2 years ago
    Hi,

    does any one how to add two numbers in one cell.
    For example,

    X
    2.2
    3.6
    0.9
    1.2

    and I want to add each number with its rank,

    Like;
    X
    2.2 (2)
    3.6 (1)
    0.9 (4)
    1.2 (3)
    and these rank must be bold and with different colour.
  • To post as a guest, your comment is unpublished.
    SANDEEP · 2 years ago
    thanks very much for KUTOOLS FOR EXCEL
  • To post as a guest, your comment is unpublished.
    Jasvanthi · 2 years ago
    I have filtered the cloumn and i have to apply TC001 till TC0209 on the filtered cells, could anyone please help me with this
  • To post as a guest, your comment is unpublished.
    Rajendra · 3 years ago
    i have single alphabets in different cell, say 10 cell with different....how can add these alphabets in one cell i.e. total no. of alphabets in one cell
  • To post as a guest, your comment is unpublished.
    sayad · 3 years ago
    arihhurthj
    hshsjsyshsj
    shshydjdj
    shshssj
    shshdj
    shshjddj

    Please tell me how to add new line with character GO after every line.
  • To post as a guest, your comment is unpublished.
    Fayyaz · 3 years ago
    Need Help,
    I want to add character in Excell
    Example
    Cell No A1 Show A so next cell A2 show B
    I want to show B in next cell
  • To post as a guest, your comment is unpublished.
    Glynis · 3 years ago
    Thank you so much :)
  • To post as a guest, your comment is unpublished.
    I wants to add text · 4 years ago
    e.g

    44444

    Out I needed as DL/44444
  • To post as a guest, your comment is unpublished.
    man in · 4 years ago
    you can also use this site
  • To post as a guest, your comment is unpublished.
    Rich · 4 years ago
    I can't concatenate a date, (excel tries to divide it like a sum).
    I have tried changing the format of the source cell and the destination, nothing will stop concatenate from dividing the date.
    I don't want to buy Kutools either. (I know there is a free trial but that is not a long term solution).
  • To post as a guest, your comment is unpublished.
    Ng · 5 years ago
    maria,
    you can edit the code by adding:
    Dim OriValue As String

    then add this code after For Each....
    OriValue = Rng.Value
    and change the Rng.Value to OriValue in equation:
    Rng.Value = addStr & OriValue
    • To post as a guest, your comment is unpublished.
      Ajay · 4 years ago
      i have a data where in column there are no,s like this
      rs 10
      rs 20
      rs 30
      rs 40
      Please let me know how can i sum them without removing "rs" from entire column
  • To post as a guest, your comment is unpublished.
    maria · 5 years ago
    if our prefix was number,how can insert it to cells contain number?
  • To post as a guest, your comment is unpublished.
    Simensch · 5 years ago
    Thanks!
    = CONCATENATE made my day!
  • To post as a guest, your comment is unpublished.
    Vishal · 5 years ago
    CAn i add KUltools without admin rights
    • To post as a guest, your comment is unpublished.
      Admin_jay · 5 years ago
      [quote name="Vishal"]CAn i add KUltools without admin rights[/quote]
      :-) You need to install the software with an admin account. After the installation, you can use it without admin account.
  • To post as a guest, your comment is unpublished.
    John Graf · 5 years ago
    Thank you, the above instructions have saved me hours of work.
  • To post as a guest, your comment is unpublished.
    The O · 6 years ago
    Thanks, Kutools saved me a lot of work :-)