How to Concatenate Number and String in Google Sheets

Today we will look at a couple of ways to concatenate number and string in Google Sheets. We will also see some advanced examples based on combining what we have learned.

But before we begin, we must first understand how strings and numbers are presented in Google Sheets. If you are already aware of it, please feel free to skip to the methods section.

Like any spreadsheet application, Google Sheets utilizes alignment to differentiate between different types of data.

Left-aligned for Text
Right-aligned for Numbers

You can of course format the data in the cells afterward to your liking. But this is the best way for users to recognize whether the value in the cell is indeed a text or a number.

Table of Contents

2 Ways to Concatenate Number and String in Google Sheets

1. The Ampersand (&): The Basic Way to Concatenate Number and String in Google Sheets

The most common way to combine or concatenate values from two cells in Google Sheets is to use the ampersand (&). It doesn’t matter what type of function it is (text or numbers), the ampersand will concatenate the two values.

Let’s see it in action in our dataset:

=B3&C3

And that’s it! But take notice of one thing: The result is left-aligned. Meaning that if we concatenate a number and a string in Google Sheets, the result will always be in the text format.

That said, let’s add a separator (or delimiter) to our formula to make it more presentable:

=B3&", "&C3

The comma (,) delimiter was added in the text format; thus, it is within the quotes (“”). And to concatenate it with the rest of the values, the ampersand (&) was used once again.

To learn more on this with added examples, please visit our How to Concatenate With Separator in Google Sheets article.

2. Using the CONCATENATE Function

Take the idea of the ampersand method and put it in a sensible function and you get the CONCATENATE function of Google Sheets.

To understand it better, let’s look at its syntax:

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

As before, we can use this function to concatenate both strings and numbers in Google Sheets. Simply input the values or value references separated by a comma in the function:

=CONCATENATE(B3,". ",C3)

Similar Readings

3. Taking Advantage of the TEXT function to Cleanup Our Results

Since the result of our concatenation of both number and string comes out in the text format, we can take advantage of the TEXT function of Google Sheets to add some customization to the numbers.

The TEXT function can help transform the number output on top of converting it into a text format. For example:

=TEXT(B3,”0.00″)

Not only does the TEXT function transform the number into text format, but it also allows us to customize the number format itself. In this case, we have transformed a recurring number into 2 decimal places.

To clarify, the TEXT function syntax is:

TEXT(number, format)

That said, we can take this capability of the TEXT function and utilize it in our concatenation of numbers and strings. We do this to better present our data in our spreadsheet.

For example, we want to find the speed of each following entry in 2 decimal places:

Our formula is:

=CONCATENATE(TEXT(D3,"#.##")," ","m/s")

=CONCATENATE(TEXT(D3,"0.00")," ","m/s")

Advanced Example: Combining Text and Formula

We have seen how to concatenate individual numbers and strings so far, but there are many formulas where the outputs are presented as numbers. What if we want to concatenate these formulas with other strings to make them more presentable?

We can. Google Sheets allows us to do that.

For example, in the following dataset, we are to find the average lap times for each session. But we are only given one cell of each session to put our answers in and in 3 decimal places.

To solve this problem, we must combine a few formulas:

AVERAGE: to find the average of the time.
TEXT: to output the average time in 3 decimal places.
CONCATENATE: to combine our average time (number) with the word “seconds” (string) after it.

Our formula:

=CONCATENATE(TEXT(AVERAGE(B3:D3),"0.000")," seconds")

Final Words

That concludes all the ways we can concatenate number and string in Google Sheets. We hope that the approaches we have discussed and the examples we have shown come in handy for your daily tasks.

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