How to combine text in Excel without spaces

August 1, 2022

A free Office suite fully compatible with Microsoft Office

Free Download

A free Office suite fully compatible with Microsoft Office

Free Download

Excel frequently requires us to interact with text data. It could consist of text strings like names, addresses, email addresses, or other data. Frequently, Excel cells containing text data must be combined.

Your data may be nearby or far away in the same spreadsheet or even in a separate worksheet, or it may be in neighbouring cells (rows or columns).

Combining Cells without a Space or Separator:

The simplest and most popular approach to merge cells in Excel is with this method. Consider the following data set as an illustration:

Columns A and B can be combined to create a string that will be a blend of both strings.

Here's how to go about it:

1.In a cell where you wish the merged string to appear, enter the following formula:


2.Copy and paste this into each cell.

You will receive what is displayed below:

4.Instead of using the ampersand, you may alternatively accomplish the same task by using the CONCATENATE function (&). The following formula would provide the same outcome:


How to Combine Cells with a Separator or Space in the Middle:

Additionally, you may mix cells with a designated separator in between. It might be a comma, a space, or any other type of separator.

Let's say we have the dataset as displayed below:

Here are the steps to add a space character between the first and last names:

1.Fill in a cell using the following formula: 

=A2& &B2

Copy and paste this into each cell.

The initial name and last name would be combined with a gap between them.

3.You can substitute another separator in the formula, such as a comma or a dot.How to Combine Cells with Interstitial Line Breaks:When combining cells in Excel, you may optionally add line breaks in addition to separators.Assume you have the dataset as follows:Various components of the address (Name, House #, Street, City, and Country) are in different cells in the example above.To combine these cells, use the & (ampersand) key or the CONCATENATE function.However, just adding these cells together would result in the following:This address is not in an acceptable format. Even if you could attempt utilising text wrap, it wouldn't be effective.What we need to do here is to put each element of the address in a separate row in the same cell. This can be achieved by using the CHAR (10) function  with the & character. CHAR (10) forces everything to move to a new line. Use the following formula to get the contents of each cell in a separate row within the same cell. =A2&CHAR(10)&B2&CHAR(10)&C2&CHAR(10)&D2&CHAR(10)&E2 This formula uses CHAR (10). Insert a line break after each cell between each cell reference. Once you have the result, you can apply the wrapping text to the cell that contains the result and you will get the following result:

Did you learn about how to combine text 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 the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience