Extract A String Between Two Characters

[theaudioguy]

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.

 

[Pr0metheus]

Now it works as expected! Thanks for support, Peter.

 

[Peter_SSs]

Now it works as expected! Thanks for support, Peter.

No problem! Thanks for letting us know.

 

[KristieNovice]

HELP

I am trying to do the same as everyone else... Am a novice at this and still getting my head around what the functions achieve so i seem to be doing something fundamentally wrong. If someone can help and also explain what I am doing wrong that would be AMAZING. I will either have a 15 or 21 digit number with 4 "_" separating the different item codes.
Such as
[TABLE="width: 1140"]
<tbody>[TR]
[TD]01_001_0101_1_1
[/TD]
[/TR]
[TR]
[TD]OR[/TD]
[/TR]
[TR]
[TD]03_050903053_0103_1_1

What I have so far is...... I am just not sure where I am going wrong.
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1140"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]To Find "_"[/TD]
[TD]"_" position[/TD]
[TD]To extract part of string[/TD]
[TD]To extract part of string[/TD]
[TD] [/TD]
[TD]Works [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]First[/TD]
[TD]SEARCH("_",A62)[/TD]
[TD="align: right"]3[/TD]
[TD]LEFT($A62,SEARCH("_",$A62)-1)[/TD]
[TD]01[/TD]
[TD] [/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Second [/TD]
[TD]SEARCH("_",A62,SEARCH("_",A62)+1)[/TD]
[TD="align: right"]7[/TD]
[TD]LEFT($A62,SEARCH("_",$A62,SEARCH("_",A62)+1))[/TD]
[TD]01_001_[/TD]
[TD] [/TD]
[TD]No [/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Third [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Fourth[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Fifth[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

HELP

I will either have a 15 or 21 digit number with 4 "_" separating the different item codes.
Such as
01_001_0101_1_1

OR

03_050903053_0103_1_1

Please clarify what you want extracted from such strings?
That is, what result(s) do you want and where do you want it/them?

 

[KristieNovice]

I need to extract each part of the string between the "_" into different cells so eg

[TABLE="width: 471"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]01_001_0101_1_1[/TD]
[TD]01[/TD]
[TD]001[/TD]
[TD]0101[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I need to extract each part of the string between the "_" into different cells so eg

[TABLE="width: 471"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[/TR]
[TR]
[TD]01_001_0101_1_1[/TD]
[TD]01[/TD]
[TD]001[/TD]
[TD]0101[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

With your data in cell A1, put this formula in cell B1 and copy it across...

=TRIM(MID(SUBSTITUTE("_"&$A1,"_",REPT(" ",99)),COLUMNS($B:B)*99,99))

This formula, and its copies across, can all be copied down as needed.

 

[KristieNovice]

Thank you .. im sure that will work but is there any chance you could explain how it works, I have lots of these kinds of things to do and if I don't understand it I am just going to get stuck again!

 

[Peter_SSs]

.. is there any chance you could explain how it works ..

This is basically the same as Rick's but I'll use this one for my explanation, using "01_001_0101_1_1" as the sample data.

=TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",100)),COLUMNS($B:B)*100-99,100))

The SUBSTITUTE takes out each of the "_" characters and replaces them with 100 space characters.

When we are in column B, COLUMNS($B:B) is 1 so we have 1*100-99 which gives 1.
So the MID functions extracts from position 1 of the expanded string to position 100. This will be "01" followed by 98 of the first 100 spaces.
TRIM gets rid of the trailing spaces leaving "01" which is the required 1st term

When we are in column C, COLUMNS($B:C) is 2 so we have 2*100-99 which gives 101.
So the MID functions extracts from position 101 of the expanded string to position 200. This will be a couple of spaces from the end of the first 100 spaces followed by "001" followed by about 95 of the second 100 spaces.
TRIM gets rid of the leading & trailing spaces leaving "001" which is the required second term

etc

 

[KristieNovice]

So there is no way to do this without first adding in all the spaces?
No wonder I couldn't get it... i know nothing about substitution.

 

[Peter_SSs]

So there is no way to do this without first adding in all the spaces?

Sure, other ways are definitely possible. In Excel there is virtually always more than one way to achieve something.
The method suggested by Rick & me was done so because it requires relatively few functions in the formula and the same formula can be used for all result columns.

Just to demonstrate some of the possibilities, here's another formula that can be copied across to all columns, but as you can see it is a much longer formula.

Excel Workbook
	A	B	C	D	E	F
1	01_001_0101_1_1	01	001	0101	1	1
2	03_050903053_0103_1_1	03	050903053	0103	1	1
Split Text (1)

Another option is for each column to have an individual formula. I'm not suggesting that each of these is the best for its column, but rather showing a variety of possible methods.

Excel Workbook
	A	B	C	D	E	F
1	01_001_0101_1_1	01	001	0101	1	1
2	03_050903053_0103_1_1	03	050903053	0103	1	1
Split Text (2)

Yet another option for you might be to avoid formulas altogether and use Excel's built-in Text-To-Columns feature found on the Data ribbon tab