Mehrab Imtiaz
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.
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.
Read More: How to Concatenate Values for IF Condition in Google Sheets
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)
Read More: How to Use CONCATENATE Function in Google Sheets
Similar Readings
- How to Concatenate Two Columns in Google Sheets
- Google Sheets QUERY Function to Concatenate Two Columns
- How to Get Opposite of Concatenate in Google Sheets (2 Ways)
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:
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")
OR
=CONCATENATE(TEXT(D3,"0.00")," ","m/s")
Read More: How to Concatenate Strings with Separator in Google Sheets
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")
Read More: How to Concatenate Text and Formula in Google Sheets (7 Ways)
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.
Related Articles
- How to Concatenate Strings in Google Sheets (2 Easy Ways)
- Concatenate Double Quotes in Google Sheets (3 Ways)
- How to Concatenate in Google Sheets (6 Suitable Ways)
- Concatenate If Cell Is Not Blank in Google Sheets (7 Ways)
- How to Concatenate Multiple Cells in Google Sheets (11 Examples)
- Append Text in Google Sheets (An Easy Guide)
