The two columns you’re combining must be next to each other to use Flash Fill. Don’t use Flash Fill if any of the following is true for your data (use the ampersand symbol or the CONCAT function instead): The columns you want to combine aren’t consecutive (e. g. , combining columns A and F). You want to be able to make changes to the original columns and have those change automatically update in the merged column.
The columns you want to combine aren’t consecutive (e. g. , combining columns A and F). You want to be able to make changes to the original columns and have those change automatically update in the merged column.
Flash Fill can detect all sorts of patterns. For example, if column A contains area codes and column B contains phone numbers, you could type the area code and phone number into column C. You can even separate the contents of the columns with words or symbols, as long as you don’t make the pattern too hard for Excel to understand. For example: A1 contains the area code 212 and B1 contains 555-1212, you could type (212) 555-1212 into column C and Excel should sense the pattern.
For example, if A2 is Maria and B2 is Martinez, Excel will suggest Maria Martinez.
If the column does not fill, press Control + E on the keyboard to activate Flash Fill manually. You can safely delete the original two columns if you’d like. The new column doesn’t contain any formulas, so you won’t lose the merged data.
Using the Ampersand & is another easy way to combine two columns. You’ll create a simple formula using & symbols into the first cell, and then apply your formula to the rest of the data to merge the whole column.
For example, if column A contains first names and column B contains last names, the " " ensures a space between the first and last names in the new column (e. g. , “Joe Williams” instead of “JoeWilliams. "
If you’d rather there not be a space between the words in the merged column, the formula would eliminate the " " and the second ampersand like this: =A1&B1 You could also place a symbol, word, or phrase inside of the quotes if you want to insert something between the two joined cells.
You can either click the cell and drag its bottom-right corner to the bottom of the columns, or double-click the square at the bottom-right corner of the cell to use autofill.
Select all the combined data you’ve created. For example, C1:C30. Press Control + C (PC) or Command + C (Mac) to copy it. Right-click the first cell in the column you just copied. Select Paste Special and choose Values.
CONCAT works just like using the ampersand symbol. Its advantage is that it’s easy to include in other formulas and is handy when making calculations. [3] X Research source If you’re just joining two columns by hand, using the ampersand is much easier.
If you’re using a version of Excel from before 2019, use CONCATENATE instead of CONCAT. [4] X Trustworthy Source Microsoft Support Technical support and product information from Microsoft. Go to source
You should now have a formula that looks something like this: =CONCAT(A1,” “,
You could also place a symbol, word, or phrase inside of the quotes if you want to insert something between the two joined cells. Alternatively, if you want the merged text to appear without a space (e. g. , JoeWilliams instead of Joe Williams), you could change the formula to =CONCAT(A1,B1).
You can either click the cell and drag its bottom-right corner to the bottom of the columns, or double-click the square at the bottom-right corner of the cell to use autofill.
Select all the combined data you’ve created. For example, C1:C30. Press Control + C (PC) or Command + C (Mac) to copy it. Right-click the first cell in the column you just copied. Select Paste Special and choose Values.
