How to Append Text With Value & Formula in Google Sheets (2 Methods)

Have you ever wondered how you append text with formula and value? In this article, we will learn how to append text with value and formulas in google sheets.

Append text is different from call concatenation. How? Appending a text with another text, value, or formula means that we want to join some entities at the end, start, or you can say before and after. Append text is a very useful feature in programming. In JavaScript, we use append child, append text node, and append functions very frequently in DOM manipulation. In this article, we will see multiple methods to learn how to append text with values and formulas in google sheets.

Use case of append text with value and formula in google sheets

When working with formulas and values, we often need to append both entities to display the final result, for example in one column I have some values written, and in the next, I have some currency names written, and I want to append the values before the currency name, or I can append the currency names after the values. This is how append works you can append before and after with the formula and value. We may have some values coming from the formula and we have the units for those entities written in another column (not coming from any formula), so we can append the value (coming from the formula) with the unit, and make a complete value that makes sense. So, this is why we need to learn how to append text with values and formulas in google sheets.

How to append text with value and formula in Google Sheets

To step-by-step learn how to append text with value and formula in google sheets, we have some examples below for each method. We will discuss some methods and practical examples for every method. So, let’s get into the methods for how to append text with value in google sheets.

How to append text with value and formula in google sheets – Using AMPERSAND (&) Formula

In this first section, we will see how to append text with value and formula in google sheets. We have an example in which we have some students and their marks in different subjects, we have the last column of remarks, in which we will apply the ampersand (&) formula to get the average marks, and some additional text. These all attributes will be displayed in one column and are derived using a single formula. This is how append works.

So, let’s move to our example and see the practical implementation of this method.

Step 1

Make similar sample data to follow the example

Step 2

In the last column, write the ampersand & formula along with the AVERAGE function

=A2&” has got ” &AVERAGE(B2:D2)&” % marks “

Step 3

Make sure to add spaces within the double quotes

Step 4

Hit Enter, and you’re done

Step 5

Drag down the formula to the entire column

How to Change Number Format When using Append text with value and Formula

In the above example where we calculated the average and append the cell with some text with the formula-derived value. In the last section, I kept all the values “round”, that’s why you didn’t see decimal points. But do you know? if there are no round values then the decimal points will look terrible and they cannot be controlled normally by using number formats or using the Decrease Decimal Places button on the main menu. So how we will format them to look clean and readable.

Let’s have a look, at how it looks when you have an average of a student with default decimal points.

You have seen how bad it looks when we have a lot of decimal points with our value. Don’t worry, we will resolve this issue and take control of the Number Format.

We will use the TEXT function to resolve this problem. TEXT function gives us full control over the formatting of the values coming from the formulas or appending with some other text and values.

Let’s see how we will use the TEXT function with our above formula to control the decimal points.

Step 1

Understand the TEXT function

=TEXT(Number, format)

=TEXT(13.5431,”#.##”)

Many characters represent different formats, you can easily find them online.

Here I am using #.# – It will represent only 1 decimal point.

Step 2

Add TEXT to the above formula

It will not work because the number of arguments is exceeded two.

Step 3

Add an ARRAY FORMULA to solve this problem

Step 4

The final formula is

=ARRAYFORMULA(CONCATENATE(A2,” has got “,(TEXT(CONCATENATE((AVERAGE(B2:D2))),”#.#”) )))&” % marks”

Step 5

Hit Enter and you’re done.

You have seen we have endless possibilities in google sheets, we have created a very complex custom formula using ARRAY FORMULA, CONCATENATION function, TEXT function, again CONCATENATION function, AVERAGE function, and then AMPERSAND formula.

It is not difficult simple you should know how to make the logic.

How to append text with value and formula in google sheets – Using CONCAT Function

In this first section, we will see the Concat function, and we will learn how to append text with values and formulas using this function. Now Concat is a simple function. It is very easy to use and can be used frequently.

The formula is built-in in google sheets.

To implement the appended example using concat, we have another similar example in which we have students’ marks, and we will find out the maximum marks of each student with their names and some additional text. Let’s see how it works in detail.

Step 1

Make similar sample data to follow the example

Step 2

Write the concat function along with MAX Function.

=CONCATENATE(A2,” has got highest marks “,MAX(B2:D2))

Step 3

Make sure you have added text with space

Step 4

Press Enter Key, and you’re done.

Step 5

Drag the formula to the entire column

This is how you can easily use these two methods to append any text with formulas or values.

So, in this article, I showed you how to use append features, and how we can have formulas, values, and text in a single column by using one-time formula.

Notes

FAQ

Why do we use the text function in google sheets?

The TEXT function is a built-in google sheets function that helps us to convert the Number format of the numbers. Now you can say we can directly change the number formats from the main menu > Format > Number. Yes, that’s for static cell values, but what if you have an appended value that has text, numbers, and other values, the number format will not work on such a cell that has multiple data types and formats. so, we have a TEXT function that can be used as a function, and passing the value in it will give you the desired format for any number, or a part of a numeric or string value.

What is the append function in google sheets?

Append is not a function, append is not a formula, append is a concept that teaches you to join two or more entities in a chained series, like append b after a, append a before b. This is the pattern of the append concept. We use concat, ampersand &, concatenation, and many more techniques to use the concept of Append.

What is the difference between join, append, and merge in google sheets?

See the below comparison.

Join means to combine two values 1 and 2 will become 12 (not twelve)

Append means to connect the chain of two more entities formula, value, the text will become value by formula + original value + text

Merge means to merge the cells, the range A1-B1 will become one cell after merging. Similarly for columns. The merge is not a numerical or calculation feature, it’s a design feature when we need to have a long main heading, and small sub-headings, so we can merge 4-5 cells in one row to make a big heading, and below we can use single cells for each sub-heading.

Conclusion

Wrapping up how to append text with value and formula in google sheets, we have learned two methods. the first one was using the ampersand & formula, and the second one was using the concat function. The outcome of this article is not these formulas but the concept of append. Append is a popular keyword used in many programming and scripting languages. Append has a unique concept that helps you add text with the formula-driven values and any function that is calculating something can be used along with multiple texts anywhere between.

The best practice is to use the above formulas when you want some additional text such as remarks with some sort of calculation like average, and also along with the name, which I took from column A.

I hope you find this article helpful. Thanks for reading, spreading the word, and keep learning with Office Demy.