Add text to Google Sheets cells at a certain position: at the beginning/end, before/after characters

Natalia Sharashova by , updated on

This tutorial will teach you how to add text in Google Sheets at the same position of all cells at once: at the beginning, at the end, after the N-th character, before or after particular characters.

As it happens, there's no direct standard way to insert text strings and characters to a certain position of a Google Sheets cell, not to mention to all selected cells at once. But here it goes — several workarounds to add text to the desired positions. They include some standard functions and a special Add text add-on.

Formulas to add text in Google Sheets

Though formulas in spreadsheets require a bit of learning, they can solve lots 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 is usually used to combine two 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 is used to merge 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 records will look like formulas, I will do that in the neighbouring column. Here's what I enter to B2:

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

Note. I enter space after +1 in the same double-quotes since the formula doesn't separate records by default.

I could copy that formula down the column to fill 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

The CONCATENATE function also exists in spreadsheets so you could merge text strings. But unlike the ampersand, the function makes you list the units to merge as its arguments:

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

Tip. If you want to learn the function inside out, we have an entire blog post devoted to it.

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 if Google Sheets doesn't offer to auto-fill it for you 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, you can use the same concatenation methods as for inserting text at the beginning of cells — an ampersand (&) and the CONCATENATE function.

Use ampersand to add text in Google Sheets

Let's see how the ampersand is used to add text or any characters to the end of the 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 replaces a part of the text in a cell with some other text.

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 use that to your advantage and replace one string with the other while inserting new characters 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.

It's the 2nd argument that requires the most attention and a learning curve. Let's see.

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

I'm going to make these numbers look presentable by wrapping the country code into brackets and adding 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 of all, everything you enter here must always be enclosed in double-quotes.

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

    A period and an asterisk in brackets is my regular expression: period stands for any character, asterisk — for any number of such characters, and brackets are required to group those characters.

    Note. There are lots and lots of regular expressions that will help you locate the text in various positions in cells. Many people use them daily, hence, there are special pages on the correct syntax of these regular expressions.

  • "$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. This is required so that the formula could return everything before and after 202 from that cell intact.

Phew!

Don't worry if it still looks difficult — this is not the easiest thing to understand. Feel free to jump to the last and the easiest solution right away or bear with me for I'm going to insert those 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 go ahead and add a dash after 555: +1 (202) 555-0158. If you count the characters, you will see that the dash is going to be inserted at the same position in all the 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 :) Of course, your use case may be different and may require another 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

There's a more elegant, quicker and user-friendly solution available to insert text in Google Sheets to any part of your cells. The add-on is called Add text and is 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.

I hope that by now you know more about adding text in Google Sheets at different positions of cells. If you have any questions left, I'll be waiting for them in the comments section below :) See you in the next blog post!

Table of contents