CODEWITHSUNDEEP
excelreplace
Clause Helper
Clause Helper

Reputation: 13

Excel: Replacing Everything After Word in Other Column

I have 2 columns in excel like this

Column1

hxxp://url.com/something-WORD-/

hxxp://url.com/something-else-WORD2-/

Column2

Word

Word2

I would like to search for the word in Column2 in Column1 and if found replace it and everything else after it with nothing so column 3 would look like

Column3

hxxp://url.com/something

hxxp://url.com/something-else

Any help is appreciated as I can't find a solution anywhere, thanks.

Upvotes: 1

Views: 472

Answers (1)

Tim
Tim

Reputation: 35933

Let column 3 have the forumla:

=LEFT(A1,FIND(B1,A1)-1).

If column 2 has the value Word

Then column 3 will be

hxxp://url.com/something-

You can subtract an extra digit in the forumla if the value in column 2 is always preceded by a dash you want removed as well.

You can wrap this in some =ISERR checking as well if you expect that the value in column 2 may not be found.

Upvotes: 1

Related Questions