Extract A String Between Two Characters

theaudioguy

New Member
Joined
Jan 27, 2010
Messages
27
I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I don't want to scare you but here is the formula, if you insist on doing it in a single formula. This is a mega-formula that I built up from several smaller formulas. It will work for any lengths. It is very difficult to read and maintain.

=LEFT(RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))),FIND("_",RIGHT(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))),LEN(RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))-FIND("_",RIGHT(RIGHT(A1,LEN(A1)-FIND("_",A1)),LEN(RIGHT(A1,LEN(A1)-FIND("_",A1)))-FIND("_",RIGHT(A1,LEN(A1)-FIND("_",A1)))))))-1)

If you put your value in A1, you can build up to it using the following formulas in the cells indicated:

A2 =RIGHT(A1,LEN(A1)-FIND("_",A1))
A3 =RIGHT(A2,LEN(A2)-FIND("_",A2))
A4 =RIGHT(A3,LEN(A3)-FIND("_",A3))
A5 =LEFT(A4,FIND("_",A4)-1)

Note that each formula needs to do a find plus know the length of the result of the previous step. That's why the mega-formula is so big, because each formula replaces the previous formula twice, and you repeat the replacement process 3 times.

There might be a slightly simpler way to do this, if it comes to me I'll post again.
 
Upvote 0
With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):
Code:
B1: =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,
LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
In the above example, the formula returns: ABCD

Does that help?
 
Upvote 0
Hi and welcome to the Board
Have you given any thought to using TEXT to COLUMNS and using your "_" as the delimiter.

Regards
Michael M
 
Upvote 0
I've read a bunch of things here on line but can't seem to figure out my own equation. I'm also trying to extract data between two areas of an URL.

Here are some examples of the portions of URLS I'm working with:

CFID=323428940&CFTOKEN
CFID=28769009&CFTOKEN
CFID=8000597&CFTOKEN

I want to extract the numbers between "CFID=" and "&CFTOKEN" but as you can see they can change from 7-9 characters each.

Does anyone have any ideas?

Thanks!
 
Upvote 0
You could just use =SUBSTITUTE(SUBSTITUTE(A1,"CFID=",""),"&CFTOKEN","")
 
Upvote 0
I have a similar question.

blahblahtext moretext, Result1: 454654654, Result Number2: ABC, Result Number3: 445344

I have the above string. I don't care about the actual results, but rather the headings. I want what is between ", " and ":" in one cell, what's between the next ", " and ":" in the next cell., etc etc

Results should look like this:
Cell1: Results 1, Cell2: Result Number2, Cell3: Results Number 3

I tried modifying Ron's formula above but just confused myself.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top