How to concatenate a range of Cells [Combine] in Excel

How to CONCATENATE a RANGE of Cells [Combine] in Excel

Table of Contents

How [CONCATENATE + TRANSPOSE] to Combine Values

The best way of  combining text from different cells into one cell is using the transpose function with concatenating function.

Look at the below range of cells where you have a text but every word is in a different cell and you want to get  all of them in one cell.

Below are the steps you need to follow to combine values from this range of cells into another cell.

  1. In B8, insert the following formula and do not press the  enter key.
    • =CONCATENATE(TRANSPOSE(A1:A5)&” “)
  2. Now, you can just select the entire inside portion of the concatenate function and press F9. It will convert it into an array.
  3. After that, remove the curly brackets from the starting to the end of the array.
  4. In the end, hit enter button.
That’s all.

How this formula works

In this formula, you will  use TRANSPOSE and space in the CONCATENATE. When you convert that reference into hard values it returns  to an array.

In this array, you have the text from each cell and a space between them and when you hit enter key, it combines all of them together.

How to Combine Text using Fill Justify Option

Fill justify is  the  most unused but most powerful  in Excel. And, whenever you need to combine text from different cells you take help of it and  use it.

The best thing is that  you need a single click to merge  the following texts. Have a look at the data  provided   below and follow the steps.

 
  1. at first, make sure to increase the width of the column where you have text.
  2. Secondly, select all the cells.
  3. At last, go to the Home Tab ➜ Editing ➜ Fill ➜ Justify.

This will merge the present  texts from all the cells into the first cell of the selection.

How to use TEXTJOIN Function for CONCATENATE Values

If you are using Excel 2016 (Office 365), there is a function known as “TextJoin”. It will make it easy for you to combine the texts from different cells into a single cell.

Syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

  1. delimiter a text string to use as a delimiter.
  2. ignore_empty true to ignore blank cell, false to not.
  3. text1 text to combine.
  4. [text2] text to combine optional.

how to use it

To combine the  values  given below, you can use the formula:

=TEXTJOIN(” “,TRUE,A1:A5)

Here you have used space as a delimiter, TRUE to ignore the blank cells and the entire range present  in a single argument. In the end, hit enter key and you’ll get all the following text in a single cell.

How to Combine Text with Power Query

Power Query is a fantastic tool and we all love it. Make it sure to check out this tutorial (Excel Power Query Tutorial). You can also use it for combining the text from a list in a single cell. Below are the steps.

  1. Select the range of the cells and click on “From table” in data tab.
  2. It will edit your following data into Power Query editor.
  3. Now from here,you have to  select the column and go to “Transform Tab”.
  4. From the  “Transform” tab, go to the  Table and click on “Transpose”.
  5. For this, you have to select all the columns (select first column, press and hold shift key, click on the last column) and press right click on the mouse and then select “Merge”.
  6. After that, from Merge window, select space as a separator and also select the  name the column.
  7. In the end, press OK and click on the “Close and Load”.

Now you will have a new worksheet in your workbook with all the texts present there  in a single cell. The best thing about using Power Query is that  you don’t need to do this setup again and again.

When you update the old list with a new value you  just need to refresh your query and it will add that new value in the cell and it’s that easy.

How to combine VBA Code to Values

If you want to use a macro code for combining texts from different cells then we  have something for you. With this code, you can combine your  texts in no time. All you need to do is, select the range of cells where you have to text and run this code.

 Sub combineText()
 Dim rng As Range
 Dim i As String
 For Each rng In Selection
 i = i & rng & " "
 Next rng
 Range("B1").Value = Trim(i)
 End Sub 

Make sure of specifying your desired location in the code where you want to combine your text.

Free Excel Templates

Waterfall Chart

Waterfall Chart

A Waterfall Chart or Bridge Chart can be a great way to visualize …

account-register

Savings Account Register

You may use separate bank accounts for tracking different savings …

pto-vacation-tracker-template

Paid Time Off Tracker

Title: Keep Track of Your Paid Time Off with Our …

Weight loss chart

Weight loss chart

Track your weight loss in progress with our weight loss …

We Create Your Life Easy With Our Free Excel Templates & Spreadsheet Dashboards

Use free Excel Templates, Tools and Dashboards to create and draft a professional looking dashboards and computation sheet for your personal and business use. You can Explore Calendars, Activity Planners, Invoice Templates, Sales Forecast Sheets, Budget templates and various business information delivering Templates in Excel and Spreadsheet at XLSX Templates. 

These templates, dashboards and tools are useful in various occasions. You can also customize these templates as per your requirement. Modification of fields and data source helps in making the template more relevant.

Learn Microsoft Excel : Blogs and Articles

Learn about various tips and tricks in Microsoft Excel and Spreadsheet. Create best templates and dashboards using free tricks and tutorials in excel and spreadsheet. These tutorial posts are useful for everyone who wants to master the skills in excel and spreadsheet.