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.
1. 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 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:
- 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
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)
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:
2. 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 start with a reference to a cell with the existing record.
- Then append the new text:
=A2&", US"
- To insert the same to all other cells, ArrayFormula will also help:
=ARRAYFORMULA(A2:A7&", US")
Tip. Of course, you can add the text at both positions in all cells at the same time:
=ArrayFormula(" 1 "&A2:A7&", US")
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")
3. 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.
I'll show you how to use that and replace one string with the other while inserting new symbols in Google Sheets cells simultaneously.
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:
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")
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 :)
4. 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:
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")
- 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 for Google Sheets.
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:
- Select cells where you want to add text.
- Enter the text you want to add.
- Choose one of 5 positions where you'd like to insert your string.
Tip. You can even skip empty cells and add text only to cells with data.
- Click Run.
Apart from Add Text, Power Tools includes 40 more add-ons for spreadsheets, so it's definitely worth checking it 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!
The above is the detailed content of How to add the same text to multiple cells in Google Sheets: efficient techniques. For more information, please follow other related articles on the PHP Chinese website!

This article explains how to create drop-down lists in Excel using data validation, including single and dependent lists. It details the process, offers solutions for common scenarios, and discusses limitations such as data entry restrictions and pe

This article will guide you through the process of creating a timeline for Excel pivot tables and charts and demonstrate how you can use it to interact with your data in a dynamic and engaging way. You've got your data organized in a pivo

The article discusses methods to sum columns in Excel using the SUM function, AutoSum feature, and how to sum specific cells.

Excel can import XML data using its built-in "From XML Data Import" function. Import success depends heavily on XML structure; well-structured files import easily, while complex ones may require manual mapping. Best practices include XML

Article discusses calculating mean in Excel using AVERAGE function. Main issue is how to efficiently use this function for different data sets.(158 characters)

Article discusses creating, formatting, and customizing tables in Excel, and using functions like SUM, AVERAGE, and PivotTables for data analysis.

The article details steps to create and customize pie charts in Excel, focusing on data preparation, chart insertion, and personalization options for enhanced visual analysis.

Article discusses creating, editing, and removing drop-down lists in Excel using data validation. Main issue: how to manage drop-down lists effectively.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

SublimeText3 English version
Recommended: Win version, supports code prompts!

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

SublimeText3 Linux new version
SublimeText3 Linux latest version
