How to Use the SUBSTITUTE Function in Excel to Combine and Separate Columns

The SUBSTITUTE function in Excel is a text function that can replace text in a given string by matching, not position. You can use this function to combine or separate columns of data based on a specific delimiter, such as a comma, a dash, or a space.

The syntax of the SUBSTITUTE function is:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

The arguments are:

• text: The text or the reference to a cell containing text for which you want to substitute characters.
• old_text: The text you want to replace.
• new_text: The text you want to replace old_text with.
• instance_num: (Optional) Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

The SUBSTITUTE function is case-sensitive and does not support wildcards.

Procedures

To use the SUBSTITUTE function to combine or separate columns, you need to follow these steps:

• Identify the delimiter that separates or joins the columns of data. For example, if you have a column of names in the format “First Name, Last Name”, the delimiter is a comma.
• To combine columns, use the SUBSTITUTE function to replace the delimiter with an empty string (“”). For example, if you have two columns of names in A2:A10 and B2:B10, you can use this formula in C2 and copy it down:

=SUBSTITUTE(A2&", "&B2,", ","")

This will concatenate the two columns with a comma and a space, and then remove the comma.

• To separate columns, use the SUBSTITUTE function to replace the delimiter with another character that is not present in the data, such as a pipe symbol (|). For example, if you have a column of names in the format “First Name, Last Name” in A2:A10, you can use this formula in B2 and copy it down:

=SUBSTITUTE(A2,", ","|")

This will replace the comma and the space with a pipe symbol. Then, you can use the Text to Columns feature in Excel to split the column based on the pipe symbol.

Example

Let’s say you have a table of sales data in Excel, as shown below:

You want to use the SUBSTITUTE function to:

• Combine the date and the product columns into one column, separated by a dash (-).
• Separate the customer name column into two columns, one for the first name and one for the last name.

To do this, you can use the following formulas:

• In F2, enter =SUBSTITUTE(TEXT(A2,"mm/dd/yyyy"),"/","-")&"-"&B2 and copy it down. This will format the date as mm/dd/yyyy, replace the slashes with dashes, and concatenate it with the product column, using a dash as the delimiter.
• In G2, enter =SUBSTITUTE(C2," ","|") and copy it down. This will replace the space in the customer name column with a pipe symbol.
• Select G2:G6, and go to the Data tab, and click Text to Columns. In the wizard, choose Delimited, and click Next. Check Other, and enter | in the box. Click Next, and then Finish. This will split the customer name column into two columns, based on the pipe symbol.

The final result will look like this: