Splitting text strings by commas or spaces in Excel

Splitting text strings based on commas or spaces is a technique to divide characters in a parameter cell into different groups based on the commas or spaces contained within that cell. Let SoftwareTricks guide you on how to split text strings by commas or spaces in Excel.

To split text strings by commas or spaces, you can use a combination of functions such as LEFT, RIGHT, MID along with FIND, SEARCH.

Firstly, you need to have a basic understanding of the functions and how to use them, such as string extraction functions and the FIND function.

String extraction functions are a type of function used to retrieve a segment of characters from a specified parameter cell. These functions rely on numerical values to determine the starting position for extracting the string and the number of characters to extract from the parameter cell.

For a clearer understanding of the LEFT, RIGHT, and MID string extraction functions, along with related examples, you can visit the following link:

Extracting a string of characters in Excel

Here, we will delve into the combination of string extraction functions and the FIND or SEARCH functions. To gain a better understanding of these two functions, FIND and SEARCH, you can access the following link:

FIND and FINDB Functions in Excel

SEARCH and SEARCHB Functions in Excel

1. Splitting strings by comma or space using LEFT

Firstly, we aim to extract the character string starting from the beginning up to the position of the first comma in the data within the parameter cell, indicating the number of characters on the left side. Here, we employ the LEFT function for string extraction.

The formula for the LEFT function is:

=LEFT(original parameter cell; number of characters to extract)

Where:

Original parameter cell is the original cell from which you will extract characters.

The number of characters to extract is the count of characters starting from the first character of the character string in the original parameter cell.

So now we have the original parameter cell, which surely contains the data ready for you to split, and the remaining issue is the number of characters to extract. The question is how to obtain an accurate number to extract just enough characters before the comma (or space) to yield the desired result. And so the FIND function (or SEARCH function) will assist us in this.

The result of the FIND or SEARCH function is a Number type, so it can become an element of the string extraction function.

Both the FIND and SEARCH functions have a similar formula structure, the only difference being that FIND distinguishes between uppercase and lowercase letters while SEARCH does not. So you can flexibly use either type of search because the characters we need to find are just spaces or commas, regardless of case.

Formula for the SEARCH function:

=SEARCH('character or string to search for'; original parameter cell to search in; start searching from which character number)

Formula for the FIND function:

=FIND('character or string to search for'; original parameter cell to search in; start searching from which character number)

Where:

The character or string to search for here will be a comma or a space, but make sure to enclose the character or string in double quotation marks. The FIND function differs from the SEARCH function in that FIND distinguishes between uppercase and lowercase letters.

Start searching from which character number, you leave it as 1 so that it will start counting from the first character when searching for the character or string to search for.

From the SEARCH/FIND formula above, we will get the result as a number counting the position of the first comma or space in the parameter cell. Combined with the LEFT function, we will obtain the characters from the beginning up to the comma position, so we need to add -1 after the SEARCH/FIND formula to exclude the other space or comma.

The result you will receive will be similar to the following:

2. Splitting strings by comma or space using MID

Similarly, when you need to split text strings located between commas or spaces, you will use the MID string extraction function.

We have the formula for MID as follows:

=MID(original reference cell;starting character position, number of characters to extract)

For the original reference cell, we need not delve too deeply. However, concerning the two subsequent parameters of the function, namely the starting character position and the number of characters to extract, we continue to utilize the SEARCH function to derive the appropriate values.

Drawing from the provided example, our MID function appears as follows:

=MID(A2;SEARCH(',';A2;1)+1;SEARCH(',';A2;SEARCH(',';A2;1)+1) - SEARCH(',';A2;1)-1)

Number of characters to extract = Position of the second comma - Position of the first comma - 1

Further explanation:

The position of the second comma will yield a number equal to the amount of characters highlighted in red in line 1.

The position of the second comma will yield a number equal to the amount of characters highlighted in green in line 2.

Lastly, we subtract 1 to exclude the series of subtraction from the counting, otherwise, the final result will have an extra comma at the end.

3. Splitting the string by comma or space using RIGHT

Finally, we employ the RIGHT function to retrieve the text string after the last comma. The formula for RIGHT is:

=RIGHT(original parameter cell;number of characters to extract)

Here is the essential RIGHT formula:

=RIGHT(A2;LEN(A2)-SEARCH(',';A2;SEARCH(',';A2;1)+1))

You'll notice a rather different function here compared to the previous two, and that's the LEN function. It's quite simple, just counting the number of characters in the parameter cell. It tallies up all characters in a parameter cell and LEN function will give you the final result.

The original parameter cell remains the same, nothing has changed there. What we need to focus on here is the number of characters to extract from the string. We'll have:

Number of characters to extract = Total characters in the parameter cell - Position of the second comma (the last comma)

Further Explanation:

The total number of characters in the parameter cell refers to counting all characters in the cell using the LEN function, which includes all the red-highlighted characters in line 1.
The position of the second comma is determined by the SEARCH function, which counts all the blue-highlighted characters in line 2.

As a result, by combining these three string extraction functions mentioned above, you've successfully separated text strings based on commas in Excel. You can flexibly apply this method to split strings based on different characters or spaces by replacing the character at the position of the comma in the example above.

Thank you for reading the article by SoftwareTips on how to split text strings by commas or spaces in Excel. We hope the article has provided sufficient guidance for you to understand and apply the necessary functions for text string splitting. Wishing you every success in your endeavors!