In the previous post, we talked that how to extract substring before the first comma or space or others specific characters in excel. And this post will guide you how to extract text after the first comma or space character in a text string.
Extract text after first comma or space
Let’s see how this formula works:
The LEN function returns the number of characters in a text string in Cell B1. The returned result goes into the MID function as its num_chars argument.
The FIND function returns the position of the first comma character in Cell B1. It returns 7. And then add 1 to get the position of the first character after comma character. The returned value goes into the MID function as its start_num argument.
So far, you got the values of the start_num and num_chars arguments from above FIND and LEN formula. And then the MID function extracts a substring based on the starting position and the number of the characters that you want to extract from a text string in Cell B1.
Last, if you want to extract a string after the first space character or others specific characters in a text string in cell B2, then you just need to change the comma character to space character in the above MID function, like this:
Extract Text between Parentheses
If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
Extract Text between Brackets
If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
Extract Text between Commas
To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function…..
Extract word that starting with a specific character
Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function ….
Extract text before first comma or space
If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….
Excel MID function
The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
Excel FIND function
The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])…
Excel LEN function
The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…