0

I need to get in D1 "A,B,C".

I need a formula that will find every word in A1 and compare from B1,B2...and replace it with text from C1,C2,C3

In the image below, you start with cell A1, separate the text by commas, find each piece in column B, replace it with the corresponding value in column C, and get the final result in D1.

Results

In CSV, it would be:

"1,2,3","1","A","A,B,C"
"","2","B",""
"","3","C",""
1
  • Please note that Super User is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
    – DavidPostill
    Aug 29, 2016 at 12:44

1 Answer 1

2

In D1 enter:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,B1,C1),B2,C2),B3,C3)

enter image description here

3
  • fwiw, If OP has latest Office 365 with TEXTJOIN: =TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH(B1:B3,A1)),C1:C3,"")) as an array formula. Aug 29, 2016 at 13:26
  • 1
    The answer by Gary is specific to the example. Meaning if there were entries in A2, A3, etc. it would break. To fix, change the b1:b3 to b$1:b$3 and c1:c3 => c$1:c$3
    – bvaughn
    Aug 29, 2016 at 14:08
  • @bvaughn Very good point! Aug 29, 2016 at 14:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .