Extract a specific word from Sentence

[earthworm]

Below is the Code which I need to extract from

C021_CBVMKR

I need to extract the above from below

C021_Enquiry_CBVMKR

I tried the below formula but its not working Please help

=LOOKUP(2^15,SEARCH(C1,A1),A1)

C021_Enquiry_CBVMKR

#VALUE!

C021_CBVMKR

<tbody>
</tbody>

 

[earthworm]

I asked if the format was always the same? You answered

[COLOR=#333333][COLOR=#333333]C021_Enquiry_CBVMKR
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]C021_Enquiry-CBVMKR
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]C021 _ Enquiry _ CBVMKR
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]C021 - Enquiry - CBVMKR
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]C021-Enquiry-CBVMKR
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]C021-Enquiry Check -CBVMKR

Format does vary but the ending is same.[/COLOR][/COLOR]

Now you have _-

Are there any other possibilities?

i think text to column function is the only option , because u r now going to vba.

 

[gaz_chops]

I think the main point is to find out what the various delimiters are, in order to offer a solution.

 

[gaz_chops]

If they are the only de-limiters? Try

=LEFT(A1,4)&"_"&MID(SUBSTITUTE(SUBSTITUTE(A1,"_-","_"),"-","_"),FIND("_",SUBSTITUTE(SUBSTITUTE(A1,"_-","_"),"-","_"),8)+1,99)

 

[earthworm]

0;0;0;0;1;1;1;1;0;0;0;0;0;0;0;1;0;0;0;0;1;1;0;0;0;0;0;0;0

How can i extract the last bold numbers occurring after every last 1 in the series . This last "1" is dynamic . I.E After every last "1" i need to extract that data

 

[gaz_chops]

You didn't respond to my last post? Guess you sorted it OK?

 

[István Hirsch]

Give this a try:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"_","-"),"-",REPT(" ",50)),50))

 

[István Hirsch]

0;0;0;0;1;1;1;1;0;0;0;0;0;0;0;1;0;0;0;0;1;1;0;0;0;0;0;0;0

How can i extract the last bold numbers occurring after every last 1 in the series . This last "1" is dynamic . I.E After every last "1" i need to extract that data

You replace "*1;" (without quotes) with nothing.