Concatenate Cell With Comma Over and Over Again
What is the CONCAT Function in Excel?
The CONCAT Function in Excel is a text role that is used for combining text in cells. The part was introduced in MS Excel 2016 as a replacement to CONCATENATE office. However, you will observe that both functions are available in MS Excel for astern compatibility.
Formula
=CONCAT (text1,text2….text_n)
How to employ the CONCAT Function in Excel
It is a built-in function that can be used equally a worksheet function in Excel. Let'southward accept an case. You need to combine/concatenate the strings below:
And so hither, nosotros will use the formula below:
Using the formula in a higher place, nosotros will get the following result:
You will find that the above issue is not acceptable, as there is no space in between the characters. CONCAT function combines the strings as nosotros instruct it to do. What we got is a long string with the concatenated values running together. Let's alter this formula to add a space. To do then, nosotros need to add another argument " " (2 double quotes around a space).
Doing so will give us the space required betwixt the characters.
In the above formulas, it is necessary to add together a comma later each argument or the role will non piece of work.
Let'south consider this example to understand more nearly how this function works:
Even if the cell contains more than one grapheme, the CONCAT function will combine them as instructed. For example, for the following strings:
When we use the formula CONCAT(B11," ",C11," ",D11," ", E11," ",F11," ",G11) it will combine all characters independent in cells B11, C11, D11, E11, F11, and G11 to requite united states the post-obit effect:
Uses of the CONCAT function
To sympathise the uses of this function, let's consider a few examples:
Example 1
Allow'south presume yous downloaded data almost your suppliers in the post-obit manner:
However, you need the entire proper noun in one cell and in club of Kickoff name, Last name. In this scenario, the CONCAT function can be used.
As nosotros need the kickoff name and and so the concluding name, we would instruct the function to combine the strings in that manner. As discussed above, the formula to be used would be CONCAT(C5," ",B5).
After typing the formula in D5, we tin can drag downwardly the formula for all required cells using fill up handle.
Example 2
Allow's assume we need the proper noun of suppliers but in capital messages. CONCAT can be used along with other Excel functions to become the desired results. Now in our example, we need the proper noun Harry Potter to be combined but in upper case. In such a scenario, nosotros would use the formula:
The result will be the proper name in all majuscule letters:
Example 3
Let'south assume in this example that the suppliers gave yous the quotation per meter of the cloth you need every bit follows:
At present, y'all need the information every bit follows:
Harry Potter, $450/meter
In such a scenario, the formula to employ will be:
So here we used CONCAT formula to combine strings and create a judgement with the additional information.
Instance 4
Suppose nosotros wish to add the name, email ID, and address with line breaks, we need to use the CONCAT function along with the CHAR function. Permit'south see how:
The formula to use will exist =C5 & " " & B5 & CHAR(10) & C2 & CHAR(10) & E5 & ", " & F5 & " " & G5
In the in a higher place formula, nosotros created a line break through CHAR ten and merged values were separated using a comma (", ") and space (" ").
The result will exist:
We can use wrap text to ensure the results are displayed correctly as shown below:
Common Errors in the CONCAT function
Every bit we saw above, if nosotros practise not use spaces in between the text to separate text entries, the text entries volition run together. So, while using the CONCAT function, we need to add that extra space. To add the space, we add double quotation marks with a infinite betwixt them " ".
- Sometimes quotation marks will appear in the result. This happens when we forget to add a comma. For instance, when we input formula =CONCAT("Hullo ""Baronial") we would get the issue as Hello"August with an extra quote mark. The reason equally mentioned is that nosotros didn't add space between the arguments.
- Numbers tin be used with or without quotation marks
- Reasons for #Proper name? error. This error usually occurs when nosotros have missed the quotation mark in the text statement.
- Reasons for #VALUE! Error. This fault volition occur when the resulting string exceeds 32,767 characters (cell limit).
- This role doesn't recognize arrays so we need to listing the cell references separately.
- Autonomously from double quotes, frontward slash (/) and asterisk (*) tin can be used to dissever the concatenated strings with other characters.
Click here to download the sample Excel file
Additional Resources
Thanks for reading CFI's guide to important Excel functions! By taking the time to learn and master these functions, you lot'll significantly speed upwardly your financial analysis. To learn more, bank check out these boosted CFI resource:
- Excel Functions for Finance
- Advanced Excel Formulas Class
- Advanced Excel Formulas You Must Know
- Excel Shortcuts for PC and Mac
Source: https://corporatefinanceinstitute.com/resources/excel/functions/concat-function-in-excel/
Posting Komentar untuk "Concatenate Cell With Comma Over and Over Again"