Add text to multiple cells in Google Sheets at the same position

This tutorial will teach you to add text in Google Sheets cells at the same position. You will insert symbols and text strings at the beginning & the end of Google Sheets cells, after any N-th character, and even before or after specified characters.

You see, Google Sheets doesn't have an easy way to insert text and characters precisely where needed in Google Sheets cells. And it's even more challenging when you need to do it for a bunch of cells at once. But here are a few clever tricks to add text to the desired spots. Among them are some standard functions and a special Add text add-on.

Formulas to add text in Google Sheets

While it takes some time to get the hang of spreadsheet formulas, they're handy for a bunch of different tasks. Inserting text in Google Sheets cells is not an exception.

Insert text at the beginning of Google Sheets cells

Concatenation in spreadsheets means combining two (or more) records into one string, e.g. 'Solar' from A1 and 'Opposites' from B1 will become 'Solar Opposites' in C1.

But how is it supposed to help when you need to add the new text to Google Sheets cells that already contain some data? Pretty easy: concatenate the 'new' part with the 'old' part.

How to add text in Google Sheets cells using an ampersand (&)

Ampersand is a special concatenation character that joins values in spreadsheets. When you use it in cells, it looks like this:

="John "&"Doe"

or

=A2&B2

The result will be literally 'John Doe' or any other combo of whatever lies in A2 and B2.

I'm going to take advantage of this to insert new text at the beginning and the end of my cells.

Let's suppose I have a list of phone numbers belonging to customers in the US: A list of phone numbers.

I'm going to use the ampersand to insert a country code — +1 — at the beginning of those cells. Since I'm going to write formulas, I will do it in the neighboring column. Here's what I enter to B2:

="+1 "&A2 Add text at the beginning of Google Sheets cells.

Note. I add space after +1 inside the same double-quotes because the formula won't automatically put a space between items on its own.

I could drag that formula down the column to fill in the other cells, but I have a better idea. I will wrap the formula in B2 in ArrayFormula and change the A2 reference to the range for the entire column:

=ArrayFormula("+1 "&A2:A7) Insert text at the beginning of all cells at once.

Add text at the beginning with the CONCATENATE function

Google Sheets also has the CONCATENATE function to combine different text strings. But unlike the ampersand, the function makes you list the units to merge as its arguments:

CONCATENATE(string1, [string2, ...])

Tip. Check out this comprehensive article devoted to CONCATENATE to learn it inside out.

And that's what I'm going to do: use the new text that I want to add as one of those arguments. Look how I use the function to add the same country codes to the beginning of the phone numbers:

=CONCATENATE("+1"," ",A2)

Tip. I mention space as an individual argument — the second one in my case.

Then you just copy the formula down the column and you will have that country code added at the beginning of all cells: Use the CONCATENATE function to insert text at the beginning of Google Sheets cells.

Insert text at the end of Google Sheets cells

To add text in Google Sheets at the end of cells, use the same joining way I showed for inserting text at the beginning of cells. I mean the CONCATENATE function that uses an ampersand (&).

Use ampersand to add text in Google Sheets

Let's see how you can use the ampersand to add text or insert symbols to the end of Google Sheets cells.

This time, you need to place a reference to a cell with the existing record first, then append the new text:

=A2&", US" Add text in Google Sheets at the end of cells.

To insert the same to all other cells, ArrayFormula will also help:

=ARRAYFORMULA(A2:A7&", US") Insert text at the end of all cells at once.

Tip. Of course, you can add the text at both positions in all cells at the same time:

=ArrayFormula("+1 "&A2:A7&", US") Insert characters at the beginning and the end of all Google Sheets cells at once.

How the CONCATENATE function adds text to the end of cells

The CONCATENATE function inserts text to the end of Google Sheets cells just like it does to the beginning. Only this time mention your new text string as the last argument:

=CONCATENATE(A2,", ","US") Append text strings at the end of the cells.

How to add text in Google Sheets before/after characters

REGEXREPLACE is a Google Sheets function that swaps out a section of text in a cell with something different.

Its name actually comes from regular expression replace — that's what the acronym stands for. The formula takes your regular expressions, searches for their matches in your Google Sheets cell, and then replaces them with the text you need.

The name of the function is actually an acronym from 'regular expression replace'. That's right — the function uses regular expressions to look for a certain text in a certain position and replace it with the required string.

I'll show you how to make use of that and replace one string with the other while inserting new symbols in Google Sheets cells at the same time.

Tip. If regular expressions are the last thing you want to spend your time on, I have good news. Below I describe one user-friendly tool that will add text almost in an instant. Feel free to jump to it right away.

REGEXREPLACE needs 3 arguments:

REGEXREPLACE(text, regular_expression, replacement)
  • text — a text (or a reference to a cell with the text) that you want to change.
  • regular_expression — a combination of characters that represents a search pattern. You'll be looking for all strings that match this pattern.
  • replacement — a string to insert instead of text.

The 2nd argument is the tricky one and takes a bit of learning to get it right. Let me show you.

I have the same list of randomly generated US phone numbers: Randomly generated phone numbers.

I'm going to tidy up these numbers a bit. First, wrap the country code into brackets, then – add a couple of whitespaces: +1 (202) 5550158.

Here's the formula I should use:

=REGEXREPLACE(A2,"(.*)202(.*)","$1 (202) $2") Add a character before and after a text string.

Let me break it down into pieces for you:

  • A2 is a cell where I want to make the changes — this one is easy :)
  • "(.*)202(.*)" is my search mask. Let's dig deeper into this one.

    First, whatever you enter here, you should always enclose it in double-quotes.

    Then I tell the function to find the code — 202 — no matter what stands before or after it — (.*)

    The combination of a period and an asterisk inside brackets — that's the regular_expression I'm using. A period stands for any character, while an asterisk — for any number of such special characters in Google Sheets. The brackets group those characters together.

    Note. There is a whole bunch of regular expressions out there that will help you find text in various spots inside your cells. Since many people use regular expressions daily, there are special pages dedicated to the right way of using them.

  • "$1 (202) $2" is what I want to get instead.

    As you can see, I've already added brackets around 202. That is my main goal.

    $1 and $2 stand for those 2 groups of characters — (.*) — that I mentioned in the previous argument. So the formula will return everything before and after 202 from that cell just as they are.

Phew!

Don't worry if it still looks difficult — this is not the easiest thing to understand. Jump ahead to the easiest solution right away if you'd like, or stick with me as I show you how to insert symbols in Google Sheets. I'm going to add dashes in between numbers :)

Add text in Google Sheets after the N-th character

Tip. I prepare my data and convert all my REGEXREPLACE formulas from above to values to continue: Replace formulas with their calculated values.

I will just add a dash after 555: +1 (202) 555-0158. If you count the characters, you will see that the dash will take the same position in all cells — after the 12th character.

So for this example, I will show you the mask required to add text after the N-th character in a cell:

=REGEXREPLACE(B2,"(.{12})(.*)","$1-$2") Add text in Google Sheets after the N-th character.

  • This time, a cell I'm changing is B2.
  • What does "(.{12})(.*)" mean?

    I want to count characters starting from the left and stop on the 12th one — (.{12}) This is my first group, and I will insert my extra character right after. Then follows the second group — all remaining characters — (.*)

    Tip. Don't forget to wrap everything in double-quotes.

  • "$1-$2" — is how I add that extra dash.

    I mention the first group without changes here — $1. Then insert my character (-) and mention the second group to append it intact — $2.

And this is it :) Naturally, your situation might be unique and call for a different mask. Feel free to refer to this list of masks and their correct syntax.

Or, instead, give the next solution a try — no formulas, no masks. Just a simple add-on with 5 radio buttons to solve the task.

Formula-free way to add text in Google Sheets

Here it is: the simplest and most effective way to insert text in Google Sheets exactly where you need it. Meet Add text add-on — part of the Power Tools collection.

Tip. Watch this video to get to know the tool better, ot feel free to read the short introduction right below it.

With this add-on, you are 4 steps away from the result:

  1. Select the cells to handle.
  2. Enter the text you want to add.
  3. Choose one of 5 positions where you'd like to insert your string.
  4. Click Run.
Use Power Tools to add text by position in Google Sheets.

You can even skip empty cells and add text only to cells with data.

Power Tools also includes 30 more add-ons for spreadsheets, so it's definitely worth checking out.

By now, I hope you've got a better handle on how to add text in Google Sheets, whether it's at the start, middle, or end of your cells. If you've got any more questions, just drop them in the comments section below. I'll be there to answer. See you in the next blog post!

Table of contents

31 comments

  1. This is issue that I'm having. Here is the code: "=if(ISNUMBER(B8), INDEX(B:B,8,1))" . Now, if value in B8 is number, then it will execute INDEX, and that works fine, but if the value in B8 (it changes, sometimes it is text and sometimes it is number, I use importhtml command). Now, if it is text, then in that cell will write FALSE, or empty (if I put ""). Is it possible that it doesn't write false or leave it blank, I want the number to stay (when it shows up). So, if the value of that cell is, let say, 4, then it should write in that other cell 4, and it works, but if the value changes from 4 to let say text "home", then I'd need 4 to stay in cell (until it changes to another number), so it kind of skips FALSE and doesn't write anything in the cell if it is FALSE (just kind of exits from if statement and doesn't change anything.

    Is it possible to do something like that, to ignore FALSE in if statement?

    • Hello,

      Of course you can make it return whatever you need. You just need to tell it where to get that number from when there's text in a cell. So if you want to continue the previous numbering, just refer to a cell with that previous number rather than putting "" manually.

  2. when i am putting text in a cell, to make an estimate for a client, part of it is including a calculated time it will take. in this particular case 3.65 weeks. but when i put the amount of time (b9) into the text it pulls from the number 3.65 but shows up as the full decimal number 3.665894132456

    message on spread sheet to client

    -----( Over all, this portion of the project will contribute to about "&Patiowalkway!B9&" weeks.

    ---doesnt pull from the original number of 3.65

    it says

    -----(over all, this portion of the project will contribute to about 3.665894132456

    can someone tell me how to fix this please?

    • Hello Rob,

      Use the ROUND function:
      Over all, this portion of the project will contribute to about "&ROUND(Patiowalkway!B9,2)&" weeks.

  3. Suggestions for a dynamic reference that updates the sheet name in a formula? I have rows of formulas in a google sheet, and each row references the same set of cells in a sheet as the following rows reference in other sheets. I will want to do the same for a hundred subsequent rows. So i just need to update the sheet name in the formula. I was going to create a column with the name of the sheet to be used in that row's formulas, but I do not know how to make the sheet reference in a formula dynamic.

    • for example, in the formula

      =if(C23="Declined","",George!D$3)

      I would like to be able to replace "George" with "Dave" or "Tim" to have the formula output be related to the data on the sheet "Dave" or "Tim"

      I do not know all the names of the sheets I will add to the file in the future, so it is an undefined data set. I was going to place the name of the sheet to be referenced in a row's formulas in column A and then try to incorporate a reference to that cell into the formula, but failed.

  4. Hello Natalia,

    thanks for your contribution.

    I am trying to add a word or a sentence into another cell after a specific sentence or word. Is that possible?

    One word in one cell inserted to another after a specific string.
    I tried using REGEXREPLACE but it did not give the results i want.
    Appreciate your help
    Abbas

  5. Hello Natalia,

    Google sheet question

    I am looking to insert a sentence or a word from one cell into another cell after a certain sentence.
    Here is my example,

    Cell A2 contains "Nov'22 Awaiting renewal"

    Cell B2 contains ""Tenant has the right to terminate the contract after the first 6 months in June 2022, by providing 3 months written notice to Landlord. (Renewal Lease 2022, P. 3, Cl. 4)

    Critical Dates meeting comments:
    Aug'22 Renewal forthcoming"

    I want to copy/move the sentence from cell A2 and insert it in cell B2 under "Critical Dates meeting comments:

    I tried using REGEXREPLACE but it did not work

  6. hi... thanks for the article, any trick how to implement escaped chars link i want to add double quotes into a string: from 123 to "123"

  7. Thanks for helping the scammers sort all are phone numbers!

  8. Hi thank you for the article :) My question is what if I just want to add a word next to a number in the same bar without it loosing its value. I’m doing a sales log calculating my sales but every time I put a word next to the number of a premium I sold it doesn’t calculate into the total. but when I take the word out it calculates back into it again.

    Thank you!

  9. Thank you very much, so helpful

  10. Hello,
    Thanks for great content.
    What if i want to add text from 2022 to 1900 at the and of the text?

  11. Hi!
    What if I write down the number 1500 or 730, I want it to automatically write 15:00 or 07:30. What is the code for that?

    Best regards
    Torben

    • Hi Torben,

      If you'd like the numbers to change in the same cells where you enter them, I'm afraid it's impossible. This format looks like time. And for Google to understand it as time, you should enter records with a colon right away.

      But if you're okay to enter these numbers say in column A and get the result in column B, you can prepare the formulas in column B that will add a colon after 2 characters, just like described in this part of the article. Here's an example:
      =REGEXREPLACE(A2,"(.{2})(.*)","$1:$2")

  12. Is there a way where I can add numbers depending on the word that will be used? For Example if I use the word "regular fund", then all the cells that has numbers with the word "regular fund" on the cell before it, will be added altogether.

  13. is there a command or a way to do when you put a text on a certain cell the dropdown value on the other cell will change?

  14. Hello, thank you very much for your professional and extraordinary services. To add text to variable cells by pressing the "CTRL" key, how do we perform the operation on the sheets we want? That is, only the sheets that we want to add text, not the whole
    I hope I was able to convey what I mean, thank you if you know the information, plugin or source??

    • Hello Li,

      I'm sorry but at the moment the add-on doesn't work with multiple non-adjacent cells. You will have to add text to each separate non-adjacent range/cell separately.

  15. Helpful, thank you! But

    ="+1 "&"A2

    Should not have the last double quote mark

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)