Forum Discussion

Phishdawg's avatar
Phishdawg
Brass Contributor
Jun 11, 2023

Migrate Data from Multiple Columns to one Column

I have a spreadsheet with existing data that has multiple columns collecting data that could better be collected in a single column with a dropdown. I need to create source data that has all of th...
excel
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jun 12, 2023

Phishdawg 

If you want to combine the values from multiple columns into one column using a formula, you can use the CONCATENATE or the ampersand (&) operator in Excel. Here's the correct formula to achieve that:

In cell J2 (assuming your existing data starts from row 2), enter the following formula:

=CONCATENATE(E2,F2,G2,H2,I2)

or

=E2&F2&G2&H2&I2

This formula will concatenate the values from cells E2, F2, G2, H2, and I2 into cell J2. Make sure the cells you are referring to in the formula are correct.

Drag the formula down to apply it to the rest of the cells in column J. This will combine the values from the corresponding cells in columns E, F, G, H, and I into the new column J.

If the result in column J shows the formula itself rather than the combined values, it might be because the cells in columns E, F, G, H, or I have blank cells. In that case, you can modify the formula to handle blank cells using the IF function.

Here is an example:

=IF(E2<>"",E2,"")&IF(F2<>"",F2,"")&IF(G2<>"",G2,"")&IF(H2<>"",H2,"")&IF(I2<>"",I2,"")

This modified formula will only concatenate the non-blank values from the corresponding columns.

Please note that using a formula to combine the values is a one-time action, and any changes in the original data will not be automatically reflected in the concatenated column.

Resources