How to Split Text Strings in Excel

 What is a Text String?

A text string is any sequence of characters consisting of either numbers, letters, or symbols. These strings can commonly hold meaning as in dates (05/04/15), times (06:45:59), or geographic location (Miami-FL). In each of these cases, different segments of the text strings hold a specific meaning. To find out more, check out our handy guide to "What is String Splitting?"

Splitting Text Strings

To split the data, Excel offers various methods for use depending upon the structure of your data. For dates that have uniform structure where you will want to split the string based upon number of characters or based upon a certain character (like in timestamps with the format “00:00:00”), Excel’s Text to Columns feature can expedite the string splitting process. I've shown this below.

To use this feature:

1. Select the column that contains the text you want to split, select Data > Data Tools > Text to Columns.

2. You can choose to split the text based on a delimiter (a given character), or on the width (number of characters) in the text.

• The Delimited option can split your text based upon a specific character, such as a comma or colon. Choosing this option, click OK, specify the delimiting character(s), and click OK.

• The Fixed Width option will split your text based up on character lengths that you can define. If you choose this option, click OK, click within the Data preview pane to specify where the breaks in your text string should be placed, and then hit OK.

3. Both options present a Data preview pane so you can make sure the data is being split the way you want. By selecting columns in the preview you can:

• Select the format of the column (General, Text, Data), with the Advanced button giving access to more options.
• Choose  to exclude a column by selecting Do not import this column.
• Pick the Destination of the new data in your workbook.

4. Click Finish. The split data will now appear in Excel as shown at the beginning of this section.

Alternate Excel Functions for Splitting Text Strings

An alternate means for splitting text is to use Excel’s formulas. Excel functions that are commonly used for text extraction include LEFT, RIGHT, and MID. All of these can be used to pull out specific portions of a text string.

You can use the LEFT function to extract characters from a string, referencing the number of characters to be extracted starting at the beginning of the string. It follows the syntax LEFT(CellReference,NumberOfCharacters). The RIGHT function references the number of characters to be extracted starting at the end of the string. It follows the syntax RIGHT(CellReference,NumberOfCharacters). The MID function references the number of characters to be extracted from a starting point within the string that you choose. It follows the syntax MID(CellReference,StartingNumber,NumberOfCharacters).

Using our prior example with the timestamp “06:45:59” in cell A1, we can extract the first, second, and third string segments using the following functions:

• =LEFT(A1,2)
• =MID(A1,4,2)
• =RIGHT(A1,2)

Sign up for Displayr