Log in  \/ 
x
or
x
x
Register  \/ 
x

or

Extract text between first and second commas from text strings

To extract the text between the first and second comma or the second and third comma from text strings, this tutorial will introduce some formulas for solving this task in Excel.


Extract text between the first and second commas or other delimiters from text string

If you want to extract the text between the first and second commas or other separators, the MID and SEARCH functions can help you to achieve this job, the generic syntax is:

=MID(cell, SEARCH("char",cell) + 1, SEARCH("char",cell, SEARCH("char",cell)+1) - SEARCH("char",cell) - 1)
  • cell: The cell reference or text string that you want to extract text from.
  • char: The specific separator that you want to extract text based on.

Please copy or enter the following formula into a blank cell where you want to get the result:

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

And then, drag the fill handle down to the cells that you want to apply this formula, and all the texts which between the first and second commas have been extracted, see screenshot:


Explanation of the formula:

1. SEARCH(",",A2) + 1: This SEARCH function is used to find the position of the first comma in cell A2, adding 1 means to start the extraction from the next character. It will get the number 14. This part is recognized as the start_num argument within the MID function.

2. SEARCH(",",A2,SEARCH(",",A2)+1) - SEARCH(",",A2) – 1: This part is recognized as the num_chars arguments in the MID function.

  • SEARCH(",",A2,SEARCH(",",A2)+1): This part of formula is used to get the position of the second comma, it will get the number 21.
  • SEARCH(",", A2): This SEARCH function will get the position of the first comma in cell A2. It will get the number 13.
  • SEARCH(",", A2, SEARCH(",", A2)+1) - SEARCH(",", A2)-1=21-13-1: Subtract the position of the first comma from the position of the second comma, and then subtract 1 from the result means to exclude the comma character. And the result is 7.

3. MID(A2, SEARCH(",",A2) + 1, SEARCH(",",A2,SEARCH(",",A2)+1) - SEARCH(",",A2) - 1)=MID(A2, 14, 7): Finally, the MID function will extract 7 characters in the middle of cell A2 which starts at the fourteenth character.


Notes:

1. If your text strings are separated by other delimiters, you just need to change the commas within the formula with other delimiters as you need.

2. Here is another simple formula also can do you a favor:

=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),100,100))


Extract text between the second and third commas or other delimiters from text string

Sometimes, you may need to extract the text between the second and third commas, in this case, the combination of the MID, FIND and SUBSTITUTE function can help you. The generic syntax is:

=MID(cell, FIND("#",SUBSTITUTE(cell,"char","#",2))+1, FIND("#",SUBSTITUTE(cell,"char","#",3)) - FIND("#",SUBSTITUTE(cell,"char","#",2))-1)
  • cell: The cell reference or text string that you want to extract text from.
  • char: The specific separator that you want to extract text based on.

Please copy or type the below formula into a blank cell:

=MID(A2, FIND("#",SUBSTITUTE(A2,",","#",2))+1, FIND("#",SUBSTITUTE(A2,",","#",3)) - FIND("#",SUBSTITUTE(A2,",","#",2))-1)

After pasting the formula, then drag the fill handle down to the cells to apply this formula, and all the texts between the second and third commas have been extracted as below screenshot shown:


Explanation of the formula:

1. FIND("#",SUBSTITUTE(A2,",","#",2))+1: This part is recognized as the start_num argument within the MID function.

  • SUBSTITUTE(A2,",","#",2): This SUBSTITUTE function is used to replace the second comma in cell A2 with a # character, you will get the result as this: "Apple-3000KG,Houston#Texas,US".
  • FIND("#",SUBSTITUTE(A2,",","#",2))+1: Using the FIND function to get the position of the # character within the text string which returned by the SUBSTITUE function. Adding 1 means to start the extraction from the next character. This will get the number 22.

2. FIND("#",SUBSTITUTE(A2,",","#",3)) - FIND("#",SUBSTITUTE(A2,",","#",2))-1: This part is recognized as the num_chars argument within the MID function.

  • FIND("#",SUBSTITUTE(A2,",","#",3)): This formula will return the position of the third comma, it will get the number 27.
  • FIND("#",SUBSTITUTE(A2,",","#",3)) - FIND("#",SUBSTITUTE(A2,",","#",2))-1= 27-21-1: Subtract the position of the second comma from the position of the third comma, and then subtract 1 from the result means to exclude the comma character. And the result is 5.

3. MID(A2, FIND("#",SUBSTITUTE(A2,",","#",2))+1, FIND("#",SUBSTITUTE(A2,",","#",3)) - FIND("#",SUBSTITUTE(A2,",","#",2))-1) = MID(A2, 22, 5): At last, the MID function will extract 5 characters in the middle of cell A2 which starts at the twenty-second character.


Notes:

1. If your text strings are separated by other delimiters, you just need to change the commas within the formula with other delimiters as you need.

2. Another simple formula also can help you to extract the text between the second and third comma:

=TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",100)),200,100))


Relative functions used:

  • MID:
  • The MID function is used to find and return a specific number of characters from the middle of given text string.
  • FIND:
  • The FIND function is used to find a string within another string, and returns the starting position of the string inside another one.
  • SEARCH:
  • The SEARCH function can help you to find the position of a specific character or substring from the given text string
  • SUBSTITUTE:
  • The Excel SUBSTITUTE function replaces text or characters within a text string with another text or characters.

More articles:

  • Extract Multiple Lines From A Cell
  • If you have a list of text strings which are separated by line breaks (that occurs by pressing Alt + Enter keys when entering the text), and now, you want to extract these lines of text into multiple cells as below screenshot shown. How could you solve it with a formula in Excel?
  • Extract Nth Word From Text String In Excel
  • If you have a list of text strings or sentences, now, you want to extract the specific nth word from the list as below screenshot shown. This article, I will introduce some methods for solving this job in Excel.
  • Extract Text Between Parentheses From Text String
  • If there is part of the text surrounded with the parentheses within the text string, now, you need to extract all the text strings between the parentheses as following screenshot shown. How could you solve this task in Excel quickly and easily?
  • Extract Text After The Last Instance Of A Specific Character
  • If you have a list of complex text strings that contain several delimiters (take the below screenshot as example, which contains hyphens, comma, spaces within a cell data), and now, you want to find the position of the last occurrence of the hyphen, and then extract the substring after it. This article, I will introduce some formulas for dealing with this task.

The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.