CONCATENATE Excel Formula

Author Zaheer    Category Formulas     Tags ,

First of all, what is concatenation? You think of Excel as able to carry out arithmetic functions on data. Well, you are wrong. Excel has several features that let you work with the text data as well. There are 2 ways to make concatenation formulas; it’s either the hard way or the easy way.

Suppose you had first names in the column A and the last names in the column B. your present task needs them both in similar cells. It’s simple and it’s ludicrous. Suppose you like to make automatic filenames to assign to data list that you are going to upload in online shopping cart program.

You have the entire graphics ready as thumbnails and as a regular size. You know already that you have used the SKU number which you added ‘t’ in front of the thumbnails and they are all JPEG files. You have got SKU number in the column C of the worksheet as well. You know that there should be easy way and yes there is.

Excel Concatenate Function

The Concatenate functions are one of the Excel text functions utilized to join 2 or more words or texts string together. The Syntax intended for Concatenate function is shown below:

= CONCATENATE (text1, text2 … text255)

The text 2, text 2555 could be words, blank spaces, cell references or numbers. More than 255 texts entries could be added in this concatenate function. Every entry should be separated with comma.

For example: Using Excel Concatenate Function

Note: You have to know that a concatenate function doesn’t leave blank space between other data or words.  To add spaces in between the concatenated data you will simply add space to line Text2 and Text4 of dialog box with the utilization of space bar in keyboard.  Here is how: Enter the following data into specified cells:

  • D1-4
    E1-hrs
  • Click cell D3 – it is the location where the result of the function would be displayed.
  • Click Formulas tab
  • Opt for Text Functions from ribbon to unlock the drop-down list
  • Click CONCATENATE in the list to discuss function dialog box
  • Online Text1 of dialog box, then type in the phrase “We will arrive in”
  • Online Text2 of dialog box, then press space bar to add blank space
  • Online Text3 of dialog box, click cell D1 to go into the cell reference in the dialog box
  • Online Text4 of dialog box, then press space bar to add blank space
  • OnlineText5 of dialog box, then click cell E1 to go into that cell reference in the dialog box
  • Then Click OK
  • Concatenated sentence or phrase “We will arrive in 4 hours” must come out in cell D3
  • Complete function of = CONCATENATE ( “We will arrive in”, ” “, D1,” “, E1) come into view in formula bar on top of the work sheet

How to Utilize Excel Concatenate Function?

This Concatenate function of Excel bonds text strings in solo text string. This allows you to select a variety of cells so as to make multiple strings when the cell meant to hold the result is the row which includes the chosen cells. Here are the steps which will demonstrate how you can utilize the Excel function of Concatenate:

  • The  Concatenate Functions’ syntax is expressed as:

Concatenate (text_1,text_2,text_3…text_n)

Wherein the text_1, text_2, text_3…text_n are one to thirty text items which will joined together. This value can be text strings, numbers, or references on a single cell.

  • Use ampersand as its calculation operator. You can use this kind of technique to bond text items as an alternative of Concatenate function.

Example: =A1 & A2 will bring similar result like this formula = Concatenate (A1, and A2).

  • Look for an example used for Concatenate function.

Put A2 = salmon including A3 = species and A4 = 25, then enter = Concatenate

  • Study the application of the different cell ranges intended for Concatenate function.

Enter this formula: = Concatenate (A2:A3) within cell B2.

See that Concatenate simply displays salmon within cell B2 since it simply utilizes text strings which are in results row. With this, you need to employ a suitable concatenate formula in every row so you can use fields in these rows.

  • Enter automatically the Concatenate formula.

Position the marker within a cell that you intend to use, opt for Insert menu located on its menu bar then select as well the “Function” found in the Insert secondary menu. Opt for “Text” type and then “Concatenate” function. Provide the argument as provoked and then click “OK” button so as to show the results.

Post comment