CODEWITHSUNDEEP
excelexcel-formula
LdM
LdM

Reputation: 704

Extract words before a specific pattern

I have a column with different names in the rows:

hello_world_xt_x_D3_m6
bye_bye_x_D1_m3
h1_man_xt_x_D3_m6
bonjour_no_x_D1_m12

I would like to remove the ending part which follows the pattern

_x_DN_mZ

where N is a number between 0 and 3 and Z is a number between 0 and 16.

I would like to have

hello_world_xt
bye_bye
h1_man_xt
bonjour_no

I think I should use a combination of search and trim/right, but I do not know how to apply it.

I have tried with =substitute(a2, "_x_D2_m3","") but I do not know how to extend it regardless the numbers which follows D and m

Upvotes: 0

Views: 100

Answers (3)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

You could use Wildcards (See the ? in the search string)

EDIT: replace second ? with *

Formula: =LEFT(A2,SEARCH("_x_D?_m*",A2)-1)

enter image description here

Upvotes: 3

P.b
P.b

Reputation: 11448

Would this do? =LEFT(A2,FIND("_x_",A2)-1)

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96753

With data in column A, in B1 enter:

=MID(A1,1,FIND("_x_",A1)-1)

and copy downward:

enter image description here

Upvotes: 3

Related Questions