Skip to main content

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description

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, Edge and Firefox.
Comments (1)
No ratings yet. Be the first to rate!
e-seul
e-seul
This comment was minimized by the moderator on the site
Thanks God!!!
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations