跳到主要內容

如何在Excel中的單元格範圍中添加前綴或後綴?

在某些情況下,有時您需要在所有單元格的開頭或結尾添加通用文本。 當然,您可以將前綴或後綴一一添加到每個單元格中。 但是,當有許多單元時,工作會很費力。 本文將向您展示一些有關在Excel中輕鬆將前綴或後綴添加到所選單元格區域的提示。


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

Excel的 串聯 函數可以為單個單元格快速插入前綴或後綴。

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

您可以使用串聯函數來插入前綴,後綴或後綴和前綴兩者,如下所示:

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

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

使用“自動填充”功能可以很容易地在列中填充具有相同內容的所有單元格。 但是,如何為列中的所有單元格添加相同的前綴或後綴? 比較分別為每個單元格鍵入前綴或後綴, Kutools for ExcelAdd Text 實用程序提供了一種簡單的解決方法,只需單擊幾下即可完成。


廣告添加文字前綴後綴

使用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 將幫助您快速將前綴或後綴插入任何選定範圍。

Excel的Kutools - 包含 300 多個 Excel 基本工具。 享受全功能 30 天免費試用,無需信用卡! 現在下載!

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

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


演示:使用Kutools for Excel將前綴或後綴添加到多個單元格


Excel的Kutools:超過 300 個方便的工具觸手可及! 立即開始 30 天免費試用,沒有任何功能限制。 現在就下載!

從Excel中的單元格的開頭/右邊/結尾刪除前綴或後綴(相同的字符數)

Kutools for Excel's Remove By Position 此功能使您可以輕鬆地從單元格中文本字符串的開始/結束/指定位置刪除前綴或後綴(某些字符)。


廣告刪除前綴後綴

相關文章

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!

Comments (24)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
hey,
I am doing product coding for some jeweler products but i dont know excel so much, i would like to ask a question to you with an example
like if i have any hoop earring so for earring my code id "E" and for hoop my code is "HP" and "23" for year in which design was made and "09" for the month in which the design was made and "001" for the sequence no of the design made in that month. i want to code it like "EHP2309001". could you please tell me a formula for that or do i need to prepare a different chart for it to use it with V lookup?
This comment was minimized by the moderator on the site
I have Product code which start from 1 to 99 but i want to add prefix that the product code should start from 0001 and end at 0099.
This comment was minimized by the moderator on the site
Hi, you can simply change the cell format for these cells by selecting them and pressing Ctrl + 1, and then selecting Custom on the Number tab, and typing "0000" in the Type: textbox.
This comment was minimized by the moderator on the site
asfdasdfawfe
This comment was minimized by the moderator on the site
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".
This comment was minimized by the moderator on the site
Use this formula in A2:
=IF(A1<>"","C"&IF(MONTH(A1)>=10,MONTH(A1),"0"&MONTH(A1))&DAY(A1)&RIGHT(YEAR(A1),2),"")
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
thanks very much for KUTOOLS FOR EXCEL
This comment was minimized by the moderator on the site
I have filtered the cloumn and i have to apply TC001 till TC0209 on the filtered cells, could anyone please help me with this
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
arihhurthj hshsjsyshsj shshydjdj shshssj shshdj shshjddj Please tell me how to add new line with character GO after every line.
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations