What is the CONCATENATE Function?

The CONCATENATE Function is categorized under Excel Text FunctionsFunctionsList of the many important Excel attributes for financial analysts. This cwarmth sheet covers 100s of features that are crucial to know as an Excel analyst. The feature helps to join 2 or even more strings right into one string.

You are watching: The process of joining two string values together is called ____.

As a financial analystFinancial Analyst Job DescriptionThe financial analyst task summary below provides a typical instance of all the abilities, education, and also suffer forced to be hired for an analyst task at a financial institution, school, or corporation. Perdevelop financial forespreading, reporting, and also operational metrics tracking, analyze financial information, produce financial models, we frequently address data as soon as doing financial analysis. The data is not always structured for evaluation and also we often need to combine information from one or more cells right into one cell or break-up information from one cell into various cells. The CONCATENATE feature helps us to do that.

For instance, If I get a list of tasks in this format:

*

The CONCATENATE feature helps structure information in the adhering to format:

*

Formula

=CONCATENATE(text1, , …)

The CONCATENATE attribute provides the complying with arguments:

Text1 (forced argument) – This is the initially item to join. The item have the right to be a text value, cell reference, or a number.Text2 (compelled argument) – The added text items that we wish to join. We can sign up with as much as 255 items that are up to 8192 personalities.

How to use the CONCATENATE Function in Excel?

To understand also the provides of this feature, let’s think about a couple of examples:

Example 1

Suppose you wish to make your data straightforward to comprehend for users. This feature helps to carry out that.

Using the monthly sales data below:

*

We deserve to use the formula:

*

To get the following result:

*

This outcome is unacceptable as it is tough to review. For this, we have to add spaces. We would add an area (” “) in between the linked values so that the result screens “Revenue for North zone is 10mn.” The formula to use is:

*

We acquire the following result:

*

Example 2

While analyzing and presenting information, we might regularly must join values in a way that contains commas, spaces, assorted punctuation marks, or various other personalities such as a hyphen or slash. To execute this, all we should execute is include the character we desire in our concatecountry formula, but we must encshed that character in quotation marks. A few examples are displayed below:

*

Example 3

Sometimes our information demands to be separated by a line break rather of spaces or a character as shown in the previous examples. The a lot of prevalent instance is merging mailing addresses imported from a .csv file or PDF file.

While doing so, we cannot simply kind a line break in the formula like a continual character, and also therefore a unique CHAR feature is essential to supply the equivalent ASCII code to the concatecountry formula.

Hence, in Windows, we use CHAR(10) wbelow 10 is the ASCII code for line feed.On the Mac device, we usage CHAR(13) wbelow 13 is the ASCII code for a carriage return.

In the screenswarm listed below, we place the address information in columns A through D, and we are putting them together in column E by using the concatecountry operator “&”. The unified worths are separated via a comma (“, “), space (” “) and a line break CHAR(10):

*

The formula provided was:

*

To get the result, as displayed over, we need to permit the “Wrap text” option for the results to display effectively. To perform this, usage Ctrl + 1 to open up the Style Cells dialog. Switch to the Alignment tab and inspect the Wrap message box or go to Home tab and under Alignment click on Wrap message.

Example 4

When combining values from multiple cells, we have to make some effort, as this feature does not accept arrays and also calls for a single cell reference in each discussion.

So we should use formula =CONCATENATE(B1, B2, B3, B4)or=B1 & B2 & B3 & B4.

Let’s check out how to do this conveniently making use of the TRANSPOSE function. Suppose tright here are 78 cells that need to be concatenated. In such a scenario, we require to:

Select the cell where we desire to output the concatenated selection. D1 in our example.Then enter the TRANSPOSE formula in that cell, =TRANSPOSE(A1:A78).Now in the formula bar, press F9 to replace the formula with calculated values.Delete the curly braces as displayed below:

*

Now form =CONCATENATE in front of the cell references in the Formula bar, put an open up parenthesis and also cshedding parenthesis, and also then push Get in.

See more: Why Is The Understanding Of Classification An Important Life Skill

The outcome would certainly be:

*

A few notes around the CONCATENATE Function

The CONCATENATE function will give the outcome as a text string.The attribute converts numbers to text when they are joined.The function doesn’t acknowledge arrays. Hence, we need to carry out each cell referral independently.#VALUE! error – Occurs once at least among the CONCATENATE function’s arguments is invalid.Numbers don’t have to be in quotation marks.#NAME? error – Occurs as soon as there are quotation marks absent from a Text dispute.The ampersand (&) calculation operator allows us join message items without utilizing a role. For instance, =A1 & B1 will certainly rerotate the exact same value as =CONCATENATE(A1,B1). In many type of situations, using the ampersand also operator is much faster and easier than using CONCATENATE to develop strings.

Click here to download the sample Excel file

Further resources

Thanks for reading CFI’s guide to necessary Excel functions! By taking the moment to learn and also master these attributes, you’ll significantly speed up your financial evaluation. To learn even more, inspect out these additional CFI resources: