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

or

如何將文本添加到Excel中所有單元格的開頭或結尾?

有時您可能需要將相同的特定文本添加到選區中所有單元格的開頭或結尾。 如果您將文本逐個手動添加到每個單元格,則必須耗時。 有更簡單的方法嗎? 是的,有很多簡單的技巧可以將相同的文本添加到選區中所有單元格的開頭或結尾。

使用公式將指定的文本添加到所有單元格的開始/結尾

使用VBA將指定的文本添加到所有單元格的開始/結尾處

使用Kutools for Excel將指定文本添加到所有ells的開始/結尾 好idea3

使用Kutools for Excel將指定文本添加到所有單元格的指定位置 好idea3


使用公式將指定的文本添加到所有單元格的開始/結尾

有兩個公式可以將指定的文本添加到Microsoft Excel中所有選定單元格的開頭或結尾。

方法1:&公式

進入 =“A類:”和A2 在單元格C2中,然後將填充手柄向下拖動到要使用此公式的單元格。 並且在所有單元格之前添加了特定文本,請參閱截圖:

doc添加特定文本1

方法2:連接公式

進入 =連結(“A類:”,A2) 在單元格C2中,然後將該公式拖動並複製到要使用的單元格中,請參閱截圖:

doc添加特定文本2

筆記:

1。 如果你想在每個單元格中添加其他指定的文本,只需要替換 A類: 與您的文字在兩個公式中。

2。 公式 = A2&“:A類” - =連接(A2,“:A類”) 將增加 :A類 在細胞的盡頭。

3。 兩個公式都不會直接修改選擇內容。


將相同的文本添加到Excel中每個單元格的特定位置

如何將文本或字符添加到多個單元格的開頭,或者將單詞或字符添加到單元格的末尾或在現有文本之間插入文本或字符? 同 添加文本 Kutools for Excel的實用程序,您可以快速應用以下操作:。 單擊即可在30天之內獲得功能全面的免費試用!
doc添加文本6
Kutools for Excel:擁有超過300個方便的Excel加載項,可以在未來30天免費試用,不受限制。

使用VBA將指定的文本添加到所有單元格的開始/結尾處

如果您想直接在選區的每個單元格中添加指定文本,以下VBA宏將簡化您的工作。

1。 選擇要添加指定文本的範圍;

2。 按住 其他 + F11 在Excel中的鍵,它打開 Microsoft Visual Basic for Applications 窗口。

3。 點擊 插入 > 模塊,並將以下VBA代碼粘貼到模塊窗口中。

VBA:在每個單元格的開頭添加指定的文本:

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "CN- " & c.Value 
Next
End Sub

4。 按 F5 鍵來運行這個宏。 並且所有單元格都將被添加該值 CN- 在單元格內容之前。

doc添加特定文本3

筆記: 1。 要在每個單元格末尾添加一些特定文本,請應用以下VBA代碼。

VBA:在每個單元的末尾添加指定的文本:

Sub AppendToExistingOnRight()
Dim c as range
For each c in Selection
If c.value <> "" Then c.value = c.value & "-CN" 
Next
End Sub

你會得到以下結果:

doc添加特定文本4

2。 你可以改變這個變量 "CN-" 或“-CN“的上述代碼。


使用Kutools for Excel將指定文本添加到所有單元格的開始/結尾處

Kutools for Excel's Add Text 工具將幫助您快速將指定的文本添加到選區中每個單元格的開頭或結尾。

Kutools for Excel, 與以上 300 方便的功能,讓您的工作更輕鬆。

安裝後 Kutools for Excel,請按照以下步驟操作:(免費下載Kutools for Excel!)

1。 選擇要添加指定文本的範圍。

2。 點擊 Kutools > Text > Add Text…。 看截圖:

doc添加特定文本11

3. 。 In在 Add Text 對話框中輸入您需要添加的文本 Text 框。

(1。)如果你檢查 Before first character 來自 Position 部分,具體文本將被添加到所有單元格值的前面,請參見截圖:

doc添加特定文本6

(2。)如果你檢查 After last character 來自 Position 部分,並且具體的文本將被添加到單元格值的末尾,參見截圖:

doc添加特定文本7

筆記:

1. Kutools for Excel's Add Text 工具允許您在預覽部分中預覽選擇中的更改。

2。 如果你檢查 Skip non-text cells 選項,此工具不會將單元格中的指定文本添加到非文本內容中。


使用Kutools for Excel將指定文本添加到所有單元格的指定位置

應用 Kutools for Excel's Add Text 函數,您不僅可以將指定的文本添加到單元格的開頭或結尾,還可以將指定的文本添加到單元格的指定位置。

1。 選擇要添加文本的範圍,然後單擊 Kutools > Text > Add Text.

2。 “ Add Text 對話框將顯示出來,並輸入指定的文本並指定要在框中插入文本的特定位置。 看截圖:

我在這裡輸入3 Specify 文本框意味著在字符串的第三個字符之後添加文本。

doc添加特定文本8

3。 點擊 Ok or Apply。 指定的文本已被添加到單元格的指定位置。 看截圖:

doc添加特定文本9

類型:

(1)中 Specify 文本框中,您可以使用逗號分開鍵入數字,以便同時在多個位置添加文本。
doc添加特定文本12

(2)如果你想在每個大寫字母前加上文字, 1st letter is uppercase 選項 Add Text 實用程序可以幫助你。
doc添加特定文本10

此外,您可以在每個小寫字母或每個數字字母之前添加文本。

點擊下載並免費試用Kutools for Excel Now!


演示:將文本添加到所有單元格的開頭或結尾

Kutools for Excel:比300方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


選項卡式瀏覽和編輯多個Excel工作簿/ Word文檔,如Firefox,Chrome,Internet瀏覽10!

您可能熟悉在Firefox / Chrome / IE中查看多個網頁,並通過輕鬆單擊相應的選項卡在它們之間切換。 此處,Office選項卡支持類似的處理,允許您在一個Excel窗口或Word窗口中瀏覽多個Excel工作簿或Word文檔,並通過單擊其選項卡輕鬆切換它們。 單擊免費獲得Office Tab的30天試用!

擅長

相關文章:


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.
    dkbhardwaj · 4 months ago
    thanks a lot dear for sharing information.
  • To post as a guest, your comment is unpublished.
    Courtney · 4 months ago
    Thank you! Apend and pre-pend macro works great
  • To post as a guest, your comment is unpublished.
    Amanda · 4 months ago
    I am trying to change a date formula from mm/dd/yyyy to dd/mm/yyyy. The leading zeros for months and dates less than 10 (two digits) are going away. Anyone have tips on how to easily put them back. I tried using the =month, =day, =year formulas and concatenating them. However, the leading zeros are dropping off. I would manually have to put in the leading zeros. Is there an easy way to do this? We are going to have approximately 1,000 rows of data on our file each month.
  • To post as a guest, your comment is unpublished.
    Manik · 8 months ago
    I want to make a formula that show in cell text "Profit" when another cell show +digit, and show in that cell "Loss" when another that cell show -digit
    • To post as a guest, your comment is unpublished.
      Sunny · 8 months ago
      Hi, Manik, use this =IF(B1>0, "Profit", "Loss")
  • To post as a guest, your comment is unpublished.
    AK · 1 years ago
    Thanks a lot!! I was initially using Access but this is so much easier.
  • To post as a guest, your comment is unpublished.
    Mohammad Omar Hayat · 1 years ago
    Hello, i want to add text in a formula;

    current cell value: ='DAM91-SVC'!$C$47
    i want to add text: [MHSV Sales Report 2017 (JAN-DEC).xlsx]

    therefore the cell value should read like this in the end: ='[MHSV Sales Report 2017 (JAN-DEC).xlsx]DAM91-SVC'!$C$47

    please tell me how will this be done.
    thank you
    total 1800 cell count need alteration.
    • To post as a guest, your comment is unpublished.
      Sunny · 8 months ago
      Hello, sorry to read your question so late. Here is a solution but you need to free download Kutools for Excel.

      Using the Convert Formula to Text utility to convert the formula cell to text, then apply Add Text utility to add the text string in the Specify position(1), then click Ok. After all cells have been added text, conver them to formula by click Kutools > Content > Convert Text to Formula.
  • To post as a guest, your comment is unpublished.
    Raghulan · 2 years ago
    Hi I want to add $ symbol to a field value, to even the length of 9 chars.
    Below is my requirement, please help


    FL1000 -- > $$$FL1000
    FL10003 --> $$FL10003
    • To post as a guest, your comment is unpublished.
      Deepak Singh · 11 months ago
      Use if conditation with concadiate like when chek len<9 then concdinate("$$$",FL1000),if(Len(FL1000)>8,concdinate("$$",FL1000) then repetedhte valuse
  • To post as a guest, your comment is unpublished.
    Gaine Faste · 2 years ago
    Sub AppendToExistingOnLeft()
    Dim c As Range
    For Each c In Selection
    If c.Value <> "" Then c.Value = "' " & c.Value
    ActiveCell.Offset(1, 0).Range("A1").Select
    Next

    End Sub


    Great formula, with the addition of the 5th line, it automatically switches to the next cell, providing users with the ability to use a "Ctrl + " ", to populate a large amount of data by just holding down "Ctrl+ "(any common letter of choice) ".
  • To post as a guest, your comment is unpublished.
    Leo · 2 years ago
    Um... I know the basics of Excel, definitely didn't know anything about the Microsoft Visual screen and these steps were so crystal clear to follow. I was able to create my first macro, which made the 400 accounts i needed to do this to, a BILLION times easier.

    Thank you!
  • To post as a guest, your comment is unpublished.
    RBTRIPP · 2 years ago
    Thank you for the VBA. It saved me a ton of time and effort.
  • To post as a guest, your comment is unpublished.
    Shaikh · 2 years ago
    Thank you so much. Thank once again!
  • To post as a guest, your comment is unpublished.
    Qasim · 2 years ago
    I want to display this number in percentage Exp: Volume Achieved-KG 30%

    but all it is coming is Volume Achieved-KG 30 any one can help me on this.

    ="Volume Achieved-KG "&ROUND(SUM($H$20*100),0)
  • To post as a guest, your comment is unpublished.
    VISHNU VASUDEVAN · 3 years ago
    Awesome! I am try this last day, and its works smoothly without any error...thanks for your assist..
  • To post as a guest, your comment is unpublished.
    sania · 3 years ago
    Cool. Thanks!! Works perfectly
  • To post as a guest, your comment is unpublished.
    Tanya · 3 years ago
    thanks it's great code.
    but i need some help.
    How to change this code that the change will takes effect only on cells that contain text and not numbers
  • To post as a guest, your comment is unpublished.
    Tanya · 3 years ago
    hello,
    thanks it's great code.
    but i need some help.
    How to change this code that the change will takes effect only on cells that contain text and not numbers
  • To post as a guest, your comment is unpublished.
    Michael Kane · 3 years ago
    thanks for this. You guys are legeneds
  • To post as a guest, your comment is unpublished.
    rakshitha · 3 years ago
    Thanks, adding specific letters before and after helped !!!!
  • To post as a guest, your comment is unpublished.
    Bill Bond · 3 years ago
    Many Thanks for taking the time to provide this code.
    It added some text to the beginning of every cell in a big selected column.
    It did exactly what I wanted in a few seconds.
    What was REALLY useful as well, was showing me how to actually run the code in a simple manner.
    I always thought you had to add a control then add the code into the Control's event, etc.

    Best Regards, Bill
  • To post as a guest, your comment is unpublished.
    RESHMA · 3 years ago
    i have such data and i want to give them numbers in front of them serially,
    E.G
    data result
    reshma - reshma1
    reshma - reshma2
    ritesh - ritesh1
    ritesh - ritesh2
    ritesh - ritesh 2
    sam - sam 1
    sam - sam 2
    rakesh - rakesh 1
    neha - neha 1
    neha - neha 2
    neha - neha 3
  • To post as a guest, your comment is unpublished.
    vinser · 3 years ago
    thanks a lot

    its was easy to work
  • To post as a guest, your comment is unpublished.
    Ritesh saxena · 3 years ago
    VBA is Awesome. Excellent tip. Saved huge amount of time. Thank you so much..
  • To post as a guest, your comment is unpublished.
    Fabio · 3 years ago
    The VBA trick works like a charm, no wwird formulas hanging around my sheet. Thanks a million!
  • To post as a guest, your comment is unpublished.
    reza · 3 years ago
    hi
    i want have following cells with order number and differnt cell content:

    skdljfkldf 1
    dfkgjfdgkjldkfj2
    lkgj 3
    dkjfdfjkj 4
    kjglkjgkjgrjtgrjtgr 5
  • To post as a guest, your comment is unpublished.
    sameer · 3 years ago
    i need help on below
    1.i want to add specific letters in cell -for eg: below

    in cell A1 i have 111 then after when i click button add AB infront of 111,if already any letters in front of number then no need to add AB

    2.How to restrict use to enter space and restrict length min 5 and max 6
  • To post as a guest, your comment is unpublished.
    Davy · 4 years ago
    Thank you very. It really has helped me very much. Please keep up the good work you are providing
  • To post as a guest, your comment is unpublished.
    Aayush · 4 years ago
    thank you so much. the information given above is very benficial
  • To post as a guest, your comment is unpublished.
    amr · 4 years ago
    thanks that helped a lot
    thanks again :roll:
  • To post as a guest, your comment is unpublished.
    algae · 4 years ago
    hi,
    my workbook has 60 sheets. i want to add a column A in sheets 5 to 56 and insert the name of the worksheet in each cell of col A . Each sheet has 150 rows, so 150 cells A1-A150.

    would greatly appreciate a vba soln

    tnx
  • To post as a guest, your comment is unpublished.
    scott aramaki · 4 years ago
    This was EXTREMELY helpful. And I don't use all caps very often.
  • To post as a guest, your comment is unpublished.
    Deepak Sadanand · 4 years ago
    Thank you. Helped with what I was looking for.
  • To post as a guest, your comment is unpublished.
    Shaun · 5 years ago
    I am trying the VBA, says its running. But its for 63,000 rows. Will this take a ton of time (an hour so far). That normal?
  • To post as a guest, your comment is unpublished.
    Miss Bunny · 5 years ago
    Thanks mch . It helped me a lot :)
  • To post as a guest, your comment is unpublished.
    Niranjan · 5 years ago
    where is the comment i have posted earlier
  • To post as a guest, your comment is unpublished.
    Niranjan · 5 years ago
    Hey,
    this is great. I need something more than this . can I select several rows and perform the same action on alternate cells.
    for example:if I had add hello at the end of each alternate cell
    a
    b
    c
    d
    e
    what I need is

    a hello
    b
    c hello
    d
    e hello
  • To post as a guest, your comment is unpublished.
    Arun Prasad Kumar · 5 years ago
    If you want to add a particular 'word' in all cells in between somewhere in the text, simply we can use "copy & replace" (Ctrl+F) function by selecting all the cells to be replaced in MS Excel.
    For example, if you have to insert "_code" in all the cells in a particular column containing cells having values like Species_Class1, Species_Class2,.....Species_Class100 and change them to Species_code_Class1, Species_code_Class2,.....Species_code_Class100.
    CLick "Crtl+F" --> Use Replace option.
    In 'Find what' give "Species*_Class" and in 'Replace with' give "Species_code_Class".
    Then click "Relace All".
    Cheers.....

    Arun Prasad, India
  • To post as a guest, your comment is unpublished.
    kirsten · 5 years ago
    Hey with the VBA method of adding information to the right of the already entered information, is there a way that you can only do it for selected cells and not ALL cells. I am trying to filter and run the macro on those cells only, and when I do and unfilter it still puts it on all. Thanks!
  • To post as a guest, your comment is unpublished.
    Bharathi · 5 years ago
    Excellent tip. Saved huge amount of time. Thank you so much..
  • To post as a guest, your comment is unpublished.
    handojim · 5 years ago
    Is it possible to change the text string that you're adding to the target cell to be a cell reference, so that instead of adding "LBA " to the start of each cell, you add the contents of a different cell?
    For example if I want to append the contents of cells A1:A10 to the start of the existing text in cells B1:B10??
  • To post as a guest, your comment is unpublished.
    Robert Bollinger · 5 years ago
    KU tools is so cool!! It took me .3 seconds to add the text I needed.

    Robert
  • To post as a guest, your comment is unpublished.
    Joseph kishore · 5 years ago
    Needed Help,


    actually am trying to find all numeric and before that add a paragraph mark..? would you please tell me how to done this this kutools in word or excel...?
  • To post as a guest, your comment is unpublished.
    Steve Watkins · 5 years ago
    VBA is fantastic, so powerful...a great help
  • To post as a guest, your comment is unpublished.
    Sarika Pachlore · 5 years ago
    The VBA tip was very helpful .Thanks aton for saving effort.
  • To post as a guest, your comment is unpublished.
    anurag · 5 years ago
    Thanks really help full
  • To post as a guest, your comment is unpublished.
    Andrew Jensson · 5 years ago
    The VBA trick worked like a charm, thank you!