CODEWITHSUNDEEP
excelvba
Ryan
Ryan

Reputation: 75

Excel Macro - Find a specified value in one column and replace with all the contents of another column(the length is flexible)

I have a spreadsheet with more than 3000 rows, and I want to replace specific values(v1,v2,...) in column A with the content in column B and C and ...

The length of content in column B,C,... is not fixed so it is not possible to use REPLACE function provided in Excel.
It is also impossible to edit for each row, as the content is very long. simple example:

column A 

{who} is the coach of {team} 
{who} is the coach of {team}
{who} is the coach of {team}

column B 

Alex Ferguson
Roberto Mancini
Rafael Benitez

column C

Man United 
Man City 
Chelsea

column D

Alex Ferguson is the coach of Man United
Roberto Mancini is the coach of Man City
Rafael Benitez is the coach of Chelsea

Column D is what I want(column B to replace {who}, column c to replace {team}).

Upvotes: 0

Views: 486

Answers (1)

Neil Mussett
Neil Mussett

Reputation: 708

Try this in column D: =SUBSTITUTE(SUBSTITUTE(A1,"{who}",B1),"{team}",C1)

You can also do it without the "{who}" thing in column A: =B1 & " is the coach of " & C1

Upvotes: 3

Related Questions