Extract a specific word from Sentence

earthworm

Well-known Member
Joined
May 19, 2009
Messages
759
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
C021_Enquiry_CBVMKR
C021_Enquiry-CBVMKR
C021 _ Enquiry _ CBVMKR
C021 - Enquiry - CBVMKR
C021-Enquiry-CBVMKR
C021-Enquiry Check -CBVMKR

Format does vary but the ending is same.
 
Upvote 0
thanks for the formula Your formula is static the length of right can vary .

C021-enquiry_CBVMKR1

I want the right "CBVMKR1" (of any lenght to be extracted without using text to column function.

<tbody>
</tbody>
 
Upvote 0
thanks for the formula Your formula is static the length of right can vary .

C021-enquiry_CBVMKR1

I want the right "CBVMKR1" (of any lenght to be extracted without using text to column function.

<tbody>
</tbody>

C021 _- Enquiry_-CBVMKR1

<tbody>
</tbody>
 
Upvote 0
Oops missed the "-"

=SUBSTITUTE(LEFT(A1,FIND("_",SUBSTITUTE(A1,"-","_")))&MID(A1,FIND("_",SUBSTITUTE(A1,"-","_"),FIND("_",SUBSTITUTE(A1,"-","_"))+1)+1,99),"-","_")
 
Upvote 0
C021 _- Enquiry Test_-CBVMKR1 the formula is not working on this logic . The main point is how to extract the last CBVMR1 (of any length) seperated by "_" or "-") with formula without using text to column function.
 
Upvote 0
I asked if the format was always the same? You answered
Code:
[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?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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