USE THE SUBSTITUTE FUNCTION TO COMBINE AND SEPARATE USING MICROSOFT EXCEL

To combine several columns into one column, the & operator is used. To include a separator between each part in addition to blank spaces, here is a way to specify the separator just once while using the SUBSTITUTE function.

To combine and separate at the same time:

1. In columns A to E, type any kind of data.
2. Select cells F2:F10 and type the following formula: =SUBSTITUTE(A2&" "&B2&" "&C2&" "&D2 & " "&E2," "," - ").
3. Press <-Ctrl+Enter->.

USE THE SUBSTITUTE FUNCTION TO COMBINE AND SEPARATE USING MICROSOFT EXCEL