Combining Data from two columns into one column

New Contributor

Happy New Year with what should be a simple problem.

Column A   Column B   Column C 

Mr.              Smith          Mr. Smith

Mr.              Jones           Mr. Jones


I have two columns, Title, Name.  Want to combine them into one column.  Then Delete both columns A and B.

Can't use Merge because column B would override column A.

Can't use Copy & Paste because that overrides also.

Can't use Concatenate because that references the two columns that will be deleted.

Can't use two separate worksheets because this worksheet will be used in another application so it must be standalone.


It would be easy to type the information but the actual data is over 500 names.  Hope there is a simple solution.  Thanks.

2 Replies
best response confirmed by ImagineTraveling (New Contributor)


This can be done with flashfill. Enter manually "Mr. Smith", "Mr. Jones" in cells C1:C2 and then press ctrl+E. This returns the result shown in attached file.



This works as well if you copy the results of the formula and then paste only values.


After either option you can delete column A and B.

Thank You!!! Both of these work great. After some experimenting with your instructions, I found that FlashFill is great for 2 columns only where they are right next to each other. Concatenate with Paste set to Values Only works if need more than two columns or if the data columns are not right next to each other. Thank you, this will save me hours of typing!