Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to add text to the beginning or end of all cells in Excel?

Sometimes you may need to add same specific text to the beginning or end of all cells in a selection. If you added the text to each cell manually one by one, it must be time-consuming. Are there easier ways? Yes, there are many easy tricks to add same text to the beginning or end of all cells in a selection.

Add specified text to the beginning / end of all cells with formulas

Add specified text to the beginning / end of all cells with VBA

Add specified text to the beginning / end of all ells with Kutools for Excel good idea3

Add specified text to the specified position of all cells with Kutools for Excel good idea3


Add specified text to the beginning / end of all cells with formulas

There are two formulas to add specified text to the beginning or end of all selected cells in Microsoft Excel.

Method 1: & formula

Enter the ="Class A:"&A2 in Cell C2, and then drag the fill handle down to the cells you want to aply this formula. And the specific text has been added before all of the cells, see screenshot:

doc add specific text 1

Method 2: Concatenate formula

Enter the =Concatenate ("Class A:", A2) in Cell C2, and then drag and copy this formula to the cells you want to use, see screenshot:

doc add specific text 2

Notes:

1. If you want to add other specified text in each cell, just replace the Class A: with your text in both formulas.

2. The formulas of =A2 & ": Class A" and =Concatenate (A2, ": Class A") will add : Class A at the end of the cells.

3. Both formulas will not modify the contents in selection directly.


add same text to a specific location of each cell in Excel

How could you add text or characters to the beginning of multiple cells, or add text or characters to the end of cells or insert text or characters between existing text? With Add Text utility of Kutools for Excel, you can quickly apply the following operations: . Click for full-featured free trial in 30 days!
doc add text 6
 
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in full future 30 days.

Add specified text to the beginning / end of all cells with VBA

If you want to add specified text in each cells of a selection directly, the following VBA Macro will ease your work.

1. Select the range in which you will add specified text;

2. Hold down the Alt + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following VBA code in the Module Window.

VBA: Adding specified text at the beginning of each cell:

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

4. Press F5 key to run this macro. And all of the cells will be added the value CN- before the cell contents.

doc add specific text 3

Notes: 1. To add some specific text at the end of each cell, please apply the following VBA code.

VBA: Adding specified text at the end of each cell:

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

And you will get the following result:

doc add specific text 4

2. you can change the variable "CN-" or "-CN" of the above codes.


Add specified text to the beginning / end of all cells with Kutools for Excel

Kutools for Excel's Add Text tool will help you quickly add specified text to the beginning or end of each cell in a selection.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After installing Kutools for Excel, please do as below:(Free Download Kutools for Excel Now!)

1. Select the range in which you will add specified text.

2. Click the Kutools > Text > Add Text…. See screenshot:

doc add specific text 11

3. In Add Text dialog box, enter the text you need to add in the Text box.

(1.) If you check Before first character from the Position section, and the specific text will be added in front of all the cell values, see screenshot:

doc add specific text 6

(2.) If you check After last character from the Position section, and the specific text will be added at the end of the cell values, see screenshot:

doc add specific text 7

Notes:

1. Kutools for Excel's Add Text tool allows you to preview the changes in selection in Preview section.

2. If you check the Skip non-text cells option, this tool won't add the specified text in cells with non-text content.


Add specified text to the specified position of all cells with Kutools for Excel

Applying Kutools for Excel's Add Text function, you can not only add the specified text to the beginning or end of the cells, but also you can add the specified text to the specified position of the cells.

1. Select a range you want to add text to, and then click Kutools > Text > Add Text.

2. The Add Text dialog will be displayed, and enter the specified text and specify the certain position which you want to insert the text in the boxes. See screenshot:

Here I type 3 into the Specify textbox means that add text after the third character of the string.

doc add specific text 8

3. Click Ok or Apply. The specified text has been added to the specified position of the cells. See screenshot:

doc add specific text 9

Tip:

(1) In Specify textbox, you can type numbers with comma separate to add text in multiple position at the same time.
doc add specific text 12

(2)If you want to add text before every uppercase letter, the 1st letter is uppercase option in Add Text utility can help you.
doc add specific text 10

Also, you can add text before every lowercase letter, or every number letter.

Click Download and free trial Kutools for Excel Now !


Demo: Add text to the beginning or end of all cells

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Tabbed browsing & editing multiple Excel workbooks/Word documents as Firefox, Chrome, Internet Explore 10!

You may be familiar to view multiple webpages in Firefox/Chrome/IE, and switch between them by clicking corresponding tabs easily. Here, Office Tab supports similar processing, which allow you to browse multiple Excel workbooks or Word documents in one Excel window or Word window, and easily switch between them by clicking their tabs. Click for free 30-day trial of Office Tab!

ot excel

Relative articles:


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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 · 3 months ago
    thanks a lot dear for sharing information.
  • To post as a guest, your comment is unpublished.
    Courtney · 3 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 · 7 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 · 7 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 · 1 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 · 10 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 · 2 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 · 3 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 · 4 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!