December 4, 2020

How to combine numbers and text in a cell in google sheet

In this lesson we will learn some tricks about converting the data in google sheet from one format to another. Let’s first learn how to combine a date with a text in a cell in google sheet with a simple example.

We have the below table containing a text in cell A2 and a date in cell B2. We will merge the text and date together. We can do it first by using the below formula in cell C2.

=A2&B2

But in this case the date will not be in the same format and it will converted to the digits as follows.

Now we will use the TEXT function to combine the text and date in cell D2 as follows.

=A2&text(B2,"mm/dd/yyyy")

As you can see below the difference between cell C2 and D2. In cell D2 the date is accurately combined with the help of TEXT function.

In the same way we can also combine the text and percentage using TEXT function to keep the percentage format unchanged.

See below we have combined the text in cell A3 with the percentage in cell B3 in cell C3 without using the TEXT function and in cell D3 with the below formula using TEXT function.

=A3&text(B3,"0.00%")

Now let’s see how can we convert a number to text format using TEXT function.

We have a number in cell A2. We have used the below formula to convert it to text in cell B2 as follows.

=TEXT(A2,"0000.00")

Based on the number of digits after dot in the source decimal number, the formula can be added updated.

Now we will also see how to convert this text back to number. We can do that using the VALUE function. See below we have used the below formula in cell C2.

=VALUE(B2)

Leave a Reply

Your email address will not be published. Required fields are marked *