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.

 

[toyz4me]

Hi, Could you please help with a formula to extract "P4/*" from the following examples:

Data --------------------------------------- Output
P4/3H6, ----------------------------------- P4/3H6
G6/3H4,GEN/18A12,P4/3H4, ------------ P4/3H4
P4/2C1.1,SLD/DIST, --------------------- P4/2C1.1
CEN/REVMET,P4/2C8, ------------------- P4/2C8
G8/3H3,L9/3H3,P4/3H3,R111/3H3, ---- P4/3H3
P4/3H15, --------------------------------- P4/3H15

The string I want to extract will always have a "," at the end.

Thanks in advance.

 

[mole999]

please read forum rules, new question = new post

 

[Mahesh Rajamani]

Hi, Could you please help with a formula to extract "P4/*" from the following examples:

Data --------------------------------------- Output
P4/3H6, ----------------------------------- P4/3H6
G6/3H4,GEN/18A12,P4/3H4, ------------ P4/3H4
P4/2C1.1,SLD/DIST, --------------------- P4/2C1.1
CEN/REVMET,P4/2C8, ------------------- P4/2C8
G8/3H3,L9/3H3,P4/3H3,R111/3H3, ---- P4/3H3
P4/3H15, --------------------------------- P4/3H15

The string I want to extract will always have a "," at the end.

Thanks in advance.

Try this for the desired output: =LEFT(MID(D5,FIND("P4",A2),LEN(A2)),FIND("|",SUBSTITUTE(MID(A2,FIND("P4",A2),LEN(A2)),",","|",1))-1)

Note: The data should be start from A1

 

[Anonto]

Hi,

Can anyone help me with the following,

A1. 50-1HO-34068-3CS15-H37
A2. 25-1FG-22254-1CS11-N
A3. 500-0FW+33347-12CS21-N
A4. 50-0OW-11105-1CS23-PP25
........
........

I want to extract the following data from column A to column B

B1. 3CS15
B2. 1CS11
B3. 12CS21
B4. 1CS23
......
......

Thanks in advance

 

[Michael M]

See post #72 !!!

 

[Bailey55]

I am trying to extract all text between two # and the length will vary. I have tried the formulas above and just can't get it to work.

Can anyone suggest a formula?
Thanks

 

[Rick Rothstein]

I am trying to extract all text between two # and the length will vary. I have tried the formulas above and just can't get it to work.

Will the two # signs you want the text between be the only # signs in the text?

 

[Bailey55]

Yes those will be the only two #s in the text

 

[Rick Rothstein]

Give this formula a try...

=MID(A1,1+FIND("#",A1),FIND("#",A1,FIND("#",A1)+1)-FIND("#",A1)-1)

or this one...

=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",200)),200,200))

your choice.

 

[Bailey55]

Give this formula a try...

=MID(A1,1+FIND("#",A1),FIND("#",A1,FIND("#",A1)+1)-FIND("#",A1)-1)

or this one...

=TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",200)),200,200))

your choice.

That works great thanks