Revision history [back]

You can combine strings in Excel that have a common prefix by using the CONCATENATE function along with the LEFT and RIGHT functions. Follow these steps:

1. Enter the prefix in a separate cell for reference.
2. Use the LEFT function to extract the part of the string after the prefix.
3. Use the RIGHT function to extract the part of the string before the prefix.
4. Use the CONCATENATE function to combine the two extracted strings with the prefix.

For example, let's say you have a list of products with the prefix "Product-" and you want to combine them into one string:

1. Enter "Product-" in a separate cell for reference (let's say cell A1).
2. In another cell, use the LEFT function to extract the part of the string after the prefix. For example, if the first product is "Product-1", use the formula: =LEFT(B1,LEN(B1)-LEN(A1))
3. In another cell, use the RIGHT function to extract the part of the string before the prefix. For example, if the first product is "Product-1", use the formula: =RIGHT(B1,LEN(B1)-LEN(A1))
4. Finally, use the CONCATENATE function to combine the two extracted strings with the prefix. For example, if the first product is "Product-1", use the formula: =CONCATENATE(A1,C1,D1) (where C1 and D1 are the cells where the extracted strings are).

Repeat the above steps for all the products in your list, and you will have a new column with the combined strings.