How to separate text strings by commas or spaces in Excel

Separating text strings based on commas or spaces is a trick that helps you split the characters in a parameter cell into different clusters based on commas or spaces that parameter cell contains. TipsMake will guide you how to separate text strings by commas or spaces in Excel.

How to separate text strings by commas or spaces in Excel Picture 1

To separate text strings with commas or spaces, you can use a combination of functions such as taking the string LEFT, RIGHT, MID to combine with the search function FIND, SEARCH.

First, you need to briefly understand the effect and usage of the string and FIND functions.

The string retrieve function is the type of function used to retrieve a character string located in a specified parameter cell. The parameter function relies on numbers to determine where to start the string and how many characters it will take from the parameter cell.

In order to learn more about the function to get the string LEFT, RIGHT and MID and related examples, you can access the following link.

The function takes a string in Excel

Below we will go into how to combine the string taking function and the FIND or SEARCH search function. To better understand 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. Separate strings by commas or spaces based on LEFT

First, we want to get the character string calculated from the beginning until the position of the first comma of the data in the parameter cell ie the number of characters on the left so here we use the function to get the string LEFT .

How to separate text strings by commas or spaces in Excel Picture 2

The formula for the LEFT function is:

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

Inside:

The original parameter cell is the original character cell you will get characters from.

The number of characters to retrieve is the number of characters counted from the first character of the string in the original parameter cell.

So we have the original parameter cell is probably available data for you to split the string and the remaining problem is the number of characters to get the string . The question is how to get an exact number to get enough strings before the comma (or space) to produce the result. And so the FIND function (or the SEARCH function) will help us with this.

The result of the FIND function or the SEARCH function is a number format so it can become an element of the string function.

The FIND or SEARCH search function has a similar form of formula, except that FIND has a case-sensitive uppercase and SEARCH does not. So you can flexibly use one of these two types of search because the characters we are looking for are just spaces or commas, not case sensitive.

The formula for the SEARCH function:

= SEARCH ("character or phrase to search"; original parameter cell to search; start searching from what number of characters)

The formula for the FIND function:

= FIND ("character or phrase to search"; original parameter cell to search; start searching from what number of characters)

Inside:

The character or phrase we are looking for here will be a comma or a space, but note that you must enclose that character or phrase in quotation marks. The FIND function is different from the SEARCH function in that the FIND function has a distinction between uppercase and lowercase letters.

Start searching from the number of characters, so you leave it as 1 so it will start counting from the first character when you start searching for the character or phrase to search .

From the above SEARCH / FIND formula, we will get a number that counts the position of the first comma or space in the parameter cell. But combined with the LEFT function, we will get the result from the beginning to the comma position so we need to add -1 behind the SEARCH / FIND formula so that the result does not include always spaces or commas.

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

How to separate text strings by commas or spaces in Excel Picture 3

2. Separate strings by commas or spaces based on the MID

Similarly when you need to separate the text string between commas or spaces, we will use the function to get the MID string.

We have the formula of MID is:

= MID (original reference cell; position where characters were to be taken, number of characters to be taken)

With the original reference cell, there is nothing to say too carefully, but with the following two elements of the function, the position to start taking the character and the number of characters to retrieve, we continue to take advantage of the SEARCH function to calculate the child. suitable number.

How to separate text strings by commas or spaces in Excel Picture 4

Based on the existing example, we have the MID function:

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

To get the above results, you analyze each element of the MID function in the example.

- The first is with the starting position taking characters , this component in the function of the example is:

SEARCH (","; A2; 1) +1

The starting position for the character we need must be the character immediately after the first comma, so we need to combine with the SEARCH function to search for the first comma then add 1 to get the position. start separating string is immediately after the first comma.

- The second is the number of characters to retrieve , this component in the function of the example is:

SEARCH (","; A2; SEARCH (","; A2; 1) +1) - SEARCH (","; A2; 1) -1

It looks a bit long, but it is actually a fairly simple combination of SEARCH functions together.

The number of characters to retrieve is the number of characters between the two commas in the original string, so we need the SEARCH function to count from the position after the first comma to the position before the second comma.

Number of characters to take = Number of places of second comma - Number of places of first comma - 1

How to separate text strings by commas or spaces in Excel Picture 5

Explain more clearly:

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

The position of the second comma will be a number equal to the number of blue characters of 2.

And finally we have to subtract 1 to series the minus sign from the count, otherwise the end result will have a more comma at the end.

3. Separate strings by commas or spaces based on RIGHT

Finally, we use the RIGHT function to get the text string after the final comma. We have the formula of RIGHT is:

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

How to separate text strings by commas or spaces in Excel Picture 6

We have the necessary RIGHT formula:

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

Here you can see the appearance of a function quite different from the above two function calculations, the LEN function. This function is quite simple, it is only a function that counts the number of characters of the parameter cell. In all the number of characters in a parameter cell, the LEN function counts and produces the final result.

The original parameter cell will remain the same source cell, we did not mention it, so the thing to note here is the number of characters to retrieve. We will have:

Number of characters to be taken from the string = Total number of characters of the parameter cell - The position number of the second comma (last comma)

How to separate text strings by commas or spaces in Excel Picture 7

Explain more clearly:

The total number of characters in the parameter cell means that we will count all the characters in the parameter cell with the LEN function and it is all the highlighted characters in line 1.
The position number of the second comma is determined by the SEARCH function and it is the number of all the characters highlighted in blue in line 2.

As a result, combining all three functions to get the above string, you have separated the comma-separated text string, you can flexibly apply it if you want to separate the string by other characters or spaces by Put that character in place of the comma above the article.

Thank you for reading our article TipsMake on how to separate text strings by commas or spaces in Excel. Hopefully, this article has enough knowledge and guidance necessary for you to understand and apply functions to separating text strings by characters. Wish you can do it successfully!