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?
F9
- Select C1, enter formula: =A1:A7
- highlight the formula text A1:A7
- press F9 recalculate formula, the screen display: ={"A";"B";"C";"D";"E";"F";"G"}.
- Delete =, { and }, press ENTER. C1 will display "A";"B";"C";"D";"E";"F";"G".
- Press CTRL+H, in the Find what box, type ", click Replace All.
- Change the Find what text to ;, in Replace with box, type ,, click Replace All.
PHONTIC function
- Select B1, enter ,(comma), drag the Fill Handle down to B6.
- Select C1, enter formula: =PHONETIC(A1:B7).
Read PHONTIC function syntax and usage.
CONCAT function
It's same with PHONTIC function:
- Select B1, enter ,(comma), drag the Fill Handle down to B6.
- Select C1, enter formula: =CONCAT(A1:B7).