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.

 

[Schwe]

I truly admire your consistency!

=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)

 

[dcuellar]

Any help I can get with this would be greatly appreciated. I've tried a few of the solutions in here with no success.

This is the information I have in one cell.
Project Leader: last, first: first.last@company.com
Finance Validator: last, first: first.last@company.com
Coach: last, first: first.last@company.com
Master Blackbelt: last, first: first.last@company.com
VLSS Leader: last, first: first.last@company.com
Green Belt: last, first: first.last@company.com
Champion: last, first: first.last@company.com

This is all on one line. What I need to extract from this is the "last, first" right after "Green Belt". The issue I have is that it will be in different orders. It will not always be second to last.

I would prefer an excel formula, but can do with vba.

 

[Rick Rothstein]

Any help I can get with this would be greatly appreciated. I've tried a few of the solutions in here with no success.

This is the information I have in one cell.
Project Leader: last, first: first.last@company.com
Finance Validator: last, first: first.last@company.com
Coach: last, first: first.last@company.com
Master Blackbelt: last, first: first.last@company.com
VLSS Leader: last, first: first.last@company.com
Green Belt: last, first: first.last@company.com
Champion: last, first: first.last@company.com

This is all on one line. What I need to extract from this is the "last, first" right after "Green Belt". The issue I have is that it will be in different orders. It will not always be second to last.

I would prefer an excel formula, but can do with vba.

I think this formula should work...

=TRIM(MID(LEFT(A1,SEARCH(":",A1,SEARCH("Green Belt:",A1)+11)-1),SEARCH("Green Belt:",A1)+11,99))

And this would be a VBA (UDF) solution...

Function LastFirst(FullText As String, AfterText As String) As String
  LastFirst = Trim(Split(Split(FullText, AfterText, , vbTextCompare)(1), ":")(0))
End Function

Which would be called like this.... =LastFirst(A1,"green belt:")

 

[dcuellar]

So, funny story. I wrote my post and started to get ready to leave because I thought, "There's no way I get a response today".

Unbelievable. You are awesome. How do I show some appreciation here?

 

[Rick Rothstein]

So, funny story. I wrote my post and started to get ready to leave because I thought, "There's no way I get a response today".

Unbelievable. You are awesome. How do I show some appreciation here?

You just did :wink:

 

[Pr0metheus]

With
A1: COMP_PROG_v1_ABCD_01

This formula returns the characters between the 3rd and 4th underscores (_):

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?

Thank you Ron! I need to help little bit.

I need to get this outcome (digit between 5th and 6th underscore - the result must be always 4-digit number with zeros at the beginning):

Source:
1_0_1_515F_C8B_3_EB0000_0_0_0
1_0_1_100D_451_35_C00000_0_0_0
1_0_19_1F49_CA1_3F4_EB0000_0_0_0
1_0_1_FE1_451_3560_C00000_0_0_0

Demanded result:
0003
0035
03F4
3560

This formula =TRIM(LEFT(SUBSTITUTE(MID(A13;FIND("|";SUBSTITUTE(A13;"_";"|";5))+1;
LEN(A13));"_";REPT(" ";LEN(A13)));LEN(A13))) gives me only exact number without zeros, so it's need to be tuned little bit. Any help?

 

[Rick Rothstein]

Give this formula a try...

=RIGHT("0000"&TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",100)),500,100)),4)

 

[Pr0metheus]

Thank you for fast answer Rick, this is exactly what I was looking for. Unbelievable... Thanks again!!! You're an expert.

 

[ScomDev]

Hello Rick,

Thank you for all the contribution you made. Some of them help to progress a bit further on the reporting I am working on. I have been able to extract some of the substrings of a cell (A1), but not all of them. And it is pretty messy. Could you help to come up with a simpler formula?

A1 : 01-30-2017 10:44:14.2313|TYPE_INFORMATION|MessageInformation.SomeInformation|Description of the record : MSRV A221|More description in text|
A2 : 01-30-2017 10:44:14.2313
A3 : TYPE_INFORMATION
A4 : MessageInformation.SomeInformation
A5 : Description of the record : MSRV A221
A6 : More description in text

#Formula I have been using
=RIGHT(A1,LEN(A1)-FIND("|",A1))
=RIGHT(A2,LEN(A2)-FIND("|",A2))
=RIGHT(A3,LEN(A3)-FIND("|",A3))
=SUBSTITUTE(RIGHT(A4,LEN(A4)-FIND("|",A4)),"|","")
=SUBSTITUTE(LEFT(A4,LEN(A4)-FIND("|",A4)+15),"|","")

 

[Rick Rothstein]

Hello Rick,

Thank you for all the contribution you made. Some of them help to progress a bit further on the reporting I am working on. I have been able to extract some of the substrings of a cell (A1), but not all of them. And it is pretty messy. Could you help to come up with a simpler formula?

Put this formula in cell A2 and copy it down...

=TRIM(MID(SUBSTITUTE("|"&A$1,"|",REPT(" ",LEN(A$1))),ROWS($2:2)*LEN(A$1),LEN(A$1)))