How to Quickly Combine the Contents of Multiple Cells Into One Cell in Excel

You can use the ampersand (&) operator in a formula to combine text from multiple cells into one cell, such as a first name and a last name. You can also use the CONCATENATE function to combine text from multiple cells into one cell, as shown in this post. But, if you have many cells need to quickly combine, how to do it?

For example, how to combine A1:A7 into C1 separated by commas?

How to Combine the Contents of Multiple Cells Into One Cell

F9

  1. Select C1, enter formula: =A1:A7
    Enter Formula
  2. highlight the formula text A1:A7
    Highlight the Formula
  3. press F9 recalculate formula, the screen display: ={"A";"B";"C";"D";"E";"F";"G"}.
    Press F9
  4. Delete =, { and }, press ENTER. C1 will display "A";"B";"C";"D";"E";"F";"G".
    Delete =, { and }
  5. Press CTRL+H, in the Find what box, type ", click Replace All.
    Replace Double Quotation
  6. Change the Find what text to ;, in Replace with box, type ,, click Replace All.
    Replace Semicolon with Comma

PHONTIC function

  1. Select B1, enter ,(comma), drag the Fill Handle down to B6.
    Drag the Fill Handle Down to B6
  2. Select C1, enter formula: =PHONETIC(A1:B7).
    PHONETIC function

Read PHONTIC function syntax and usage.

Leave a Reply

Your email address will not be published. Required fields are marked *