Format Cell as Text in Google Sheets (3 Simple Scenarios)

In this article, we will look at a few scenarios to format a cell as text in Google Sheets.

There are many reasons why we’d want to do that, of which, the most important is to present a type of data that’d otherwise show a different value.

But, before we discuss those scenarios, let’s understand some basics for data types in Google Sheets.

How to Recognize Whether a Cell in Google Sheets has a Text Value or Not?

By default, any text value input in a cell of a Google Sheets Worksheet will be left aligned. The other type, numbers, will be right aligned.

text values are formatted to the left of the cell in google sheets by default

But what happens when the formatting of the cells is center-aligned?

You can’t visually decipher whether the value is text or not.

we cannot visually decipher the data type of a cell if it's center aligned

In such cases, we can easily check the type of value of the cell using the ISTEXT function:


using the istext function to determine whether a value is a text or not

Once we’ve recognized the type of data in the cells, it becomes quite easy to work with those values.

Thus, we come back to today’s topic of working with cells as text in a Google Sheets spreadsheet.

3 Scenarios to Format Cell as Text in Google Sheets

In the following sections, we will see 3 different scenarios and their respective processes where setting the cell value as text has advantages.

1. Format the Cell as Plain Text in Google Sheets

In the following worksheet we see a common problem faced during data entry in any spreadsheet application:

some number inputs and arrangements can be deciphered as different data types in google sheets

According to the dataset, we can see that each Department has a special ID, followed by a dash (-) and then the employee serial number. The entire thing makes up the ID.

But when a certain arrangement of values is input in a cell, Google Sheets may automatically recognize it in an unwanted format.

For example, the dataset above sees the value 10-05 as the date 5th of October. And for the value that is not a valid date, 10-32, it is set as a text.

We are obviously not inputting for a date value, nor do we want these values in the column. In such cases, it is justified to format the cell as a text in Google Sheets, so that we get the foundation value only.

Step 1: Select the cells you want to format. Then navigate to the More formats button in the toolbar.

selecting the sells and navigating to the more formats option in the toolbar

Step 2: Select the Plain text option from the list.

the plain text format changes the cell format to output only text values in google sheets

Step 3: Now enter the data in the respective cells.

format cell as plain text so that when value is input it gives a text value in google sheets

As you can see in the image above, the input is no longer seen as a date value keeping us safe from any future formatting changes during the import or export of the worksheet.

The final look after setting the cells to be center-aligned:

formatting cell values as text in google sheets can take any arrangement of number values

2. Using the Apostrophe Operator (‘) to View Cell as Text

The apostrophe operator (‘) is a lifesaver when it comes to presenting many data types as we want them.

Its use becomes that crucial since Google Sheets loves to automatically recognize certain value types without user consent.

While in 90% of cases, it’s fine and even welcome, there are some scenarios where the user may want to present a different value.

Let’s look at a couple of these and how we can use the apostrophe operator to get what we want:

I. For Numbers with a Prefix

Some numerical data may have symbolic prefixes that give them meaning. For example, it is common to see the plus symbol (+) in form of phone number data.

However, trying to input phone numbers this way does not go as planned:

the plus symbol inputted before numbers changes the final value animated

Not only does the plus (+) disappear, but Google Sheets also sees this as a formula and sets the value as a number inside a formula as seen by the equal symbol (=) in the formula bar.

The remedy is to include the apostrophe before entering the Phone Number:

inputting an apostrophe before the plus allows google sheets to show the entire data

The apostrophe not only makes the plus symbol (+) visible in the cell but also makes itself invisible from the cell so as to not intrude visibly on the user.

But it can still be seen in the formula bar.

format cell values as text using the apostrophe symbol in google sheets

II. Show Formula as Text in Google Sheets

Many users of Google Sheets want to show the formulas they are using. This can be educational and help future users to build on these formulas.

But the thing is, we cannot use traditional formatting to show formulas in a cell of Google Sheets since doing so automatically shows the result of the formula and not the formula itself:

typing in the formula will only show the results

To show the formula as the formula only, include an apostrophe (‘) before the formula (before =):

including apostrophe before a formula will make it visible in the worksheet

The results for both instances of using the apostrophe operator will be shown as text.

3. Using the TEXT function to Format the Value of a Cell as Text

While Google Sheets provides a great way to convert and format cell values as text, it is still limited in many regards.

That’s where the TEXT function comes to the rescue.

TEXT(number, format)

text function syntax in google sheets

As you can see from the syntax, the function takes a number value to format a text equivalent to that given in the format field.

the text function always outputs cell values as text format in google sheets

However, whatever format you use, the resultant value will always be in the text format.

While this may be a downside on the surface, it instead can also make the result immune to accidental formatting later.

Another plus side of using TEXT is that it leaves the original data untouched, making the function a perfect carrier of number data to different cell locations and worksheets.

That said, one of its best uses is when formatting date cells to different formats in Google Sheets.

Here is a short example that we’ve seen previously of some of the ways we can use the TEXT function to format date values.

the text function can format date cells in many different ways in google sheets

Learn More: How to Convert Date to String in Google Sheets (An Easy Guide)

Final Words

That concludes all the scenarios where we format cell values as text in Google Sheets.

Most of these scenarios can be worked with simple built-in features. But if you want more control over the formatting, the TEXT function is the way to go.

Feel free to leave any queries or advice you might have in the comments section below.

Related Articles for Reading



Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply