How to Use Concatenate in Excel

CONCATENATE is a verb that implies to unite or combine. To merge the text from multiple cells into one cell in Excel, use the CONCATENATE function. The data in your Excel workbooks isn't always organized in a way that meets your needs. It's common to desire to divide a cell's content into separate cells or, conversely, consolidate data from two or more columns into one column. Concatenation is frequently needed in Excel for tasks like merging names and address components, combining text with a formula-driven value, and formatting dates and timings. These are just a few instances. We will examine numerous Excel string concatenation algorithms in this article so that you may select the approach that is most appropriate for your worksheets.

CONCATENATE in syntax (text1, [text2], ...)

For instance:

= CONCATENATE (Stream population for , A2,  , A3,  is , A4, /mile.)

Combining Names by Using Concatenate

The simplest method to break names into distinct columns when you have a column of names with the same pattern, such as only first and last names or first, middle, and last names:

1.   Launch WPS Office and open the excel file. 2. Click the Insert Function button after selecting     cell D2.

2.   Enter CONCATENATE in the search area of the pop-up dialogue and click it to pick it.

3.   Enter A2 at Text1 and B2 at Text2 in the Function Arguments dialogue, then click OK. Now, we can see that there is no space between the whole name's two parts.

4.   Add the argument (two double quotes enclosing a space) to the formula to add a space. 6. Lastly, move the fill handle to affect additional cells with the same purpose.

Combining A Text String And A Value Calculated Using A Formula

Additionally, you can combine a text string with a formula-derived value. 

For instance, the formula below can be used to retrieve the current date:

=CONCATENATE (Today is “, TEXT (TODAY (), dd-mmm-yy))

Concatenate Cells with Space, Comma, and Other Characters

You might be required to link the values in the worksheet using commas, spaces, or other punctuation. To achieve that, adhere to the following syntax.

1.   combining two cells and a space:

=CONCATENATE (A2, ““, B2)

2.   Adding a comma to join two cells together:

=CONCATENATE (A3, “,”, B3) 

3.   combining two cells and a space:

=CONCATENATE (A4, “- “, B4)

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

Concatenate Text Strings with Line Breaks

Most frequently, punctuation and spaces, like in the above example, are used to divide the concatenated text strings. However, in some circumstances, you might need to use a line break or carriage return to separate the data.

=A2 &   & B2 & CHAR (10) & C2 & CHAR (10) & D2 & ,  & E2 &   & F2

Combining Multiple Cells/Rows/Columns by Using Format Cell Feature

When we combine data and apply the CONCATENATE function in Excel, the date format and number format such as currency format, percentage format (48.9%), decimal places, etc. are removed and shown as general numbers in the concatenation results. In this article, we will learn to concatenate data by keeping date or number formatting in Excel.

1. Select a blank cell you will output the concatenation result, and enter the formula =CONCATENATE(TEXT(A2, "yyyy-mm-dd")," ", B2) ( A2 is the cell with a date and B2 is another cell you will concatenate) into it, and press the Enter key.

2. Now drag down the formula this results below output without changing the date format.

Frequently Asked Questions (FAQs)

1. Can I concatenate strings across multiple worksheets?

In spreadsheet software like Excel or Google Sheets, each worksheet is treated as a separate entity. You can perform operations and functions within a single worksheet, but you cannot directly concatenate strings across different worksheets.

2. How can I concatenate strings with different formats?

To concatenate strings with different formats, you can use a combination of functions or operators depending on the software you are using. For example, in Excel or WPS Sheets, you can use the CONCATENATE function to combine strings of different formats. Just ensure that you format the resulting concatenated string appropriately.

3. Can I concatenate numbers and text without converting them?

No, you cannot directly concatenate numbers and text without converting them in most programming languages or spreadsheet software. You would need to convert the numbers to text before concatenating them with text.

Summary

Did you learn about how to use Concatenate excel in excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides. You can also download WPS Office to edit word documents, excel, and PowerPoint for free of cost. Download now! And get an enjoyable and easy working experience.