Splitting Strings in Excel: A Simple Guide

When working with Excel, you may encounter situations where you need to split a string into separate parts. For example, consider the following string:

orderId: 12345abc-de67-89fg-hijk-123456lmnop

If you want to separate orderId and the actual ID value, there are several methods available depending on your Excel version.

Method 1: Using TEXTSPLIT (Excel 365/2021)

If you’re using the latest versions of Excel, the TEXTSPLIT function makes it easy to split text based on a delimiter:

=TEXTSPLIT(A1, ": ")

This formula will split the content in cell A1 into two separate cells:

• One containing orderId
• The other containing 12345abc-de67-89fg-hijk-123456lmnop

Method 2: Using LEFT and RIGHT Functions (Older Versions)

For older versions of Excel that do not support TEXTSPLIT, you can use LEFT, RIGHT, and FIND functions:

Extracting the Label (orderId)

=LEFT(A1, FIND(": ", A1) - 1)

Extracting the ID

=RIGHT(A1, LEN(A1) - FIND(": ", A1) - 2)

These formulas locate the colon (:) and split the string accordingly.

Method 3: Using Text to Columns

Another easy method is the Text to Columns feature:

1. Select the cell containing the string.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next.
4. Select Other and enter : as the delimiter.
5. Click Finish.

Conclusion

Splitting strings in Excel can be done easily using built-in functions or tools. Whether you use TEXTSPLIT, formulas, or Text to Columns, the method depends on your Excel version and preference.

Have any questions? Let me know in the comments!