I have a document which has a value contain a sentence some words, I need to replace specific words within the sentance with other words that are specific in specific cells.
Sometimes there are multiple of the same values in one cell and they also can appear in different orders.
Example Doc:
As an example:
Original: Year Purchased 1994 Year Sold 2000
Replace 1: 1994 Or B2
Replace 2: 2000 Or B3
New Value 1: D2
New Value 2: E3
It would be good if I could have 2 methods of doing this, 1 looking in the original value for specific words and the 2nd looking in a cell to determine the word to be replaced.
In my head I think it should be something like this:
Option 1:
Option 2:
Come cells may contain 2 or 3 values of the same to be replaced with the same value and some cells may have 3 separate values that need to be replaced with 3 separate values.
FYI I am using Google Sheets and not Excel.
Sometimes there are multiple of the same values in one cell and they also can appear in different orders.
Example Doc:
As an example:
Original: Year Purchased 1994 Year Sold 2000
Replace 1: 1994 Or B2
Replace 2: 2000 Or B3
New Value 1: D2
New Value 2: E3
It would be good if I could have 2 methods of doing this, 1 looking in the original value for specific words and the 2nd looking in a cell to determine the word to be replaced.
In my head I think it should be something like this:
Option 1:
VBA Code:
=SUBSTITUTE(SUBSTITUTE(A2,"b2","c2"),"d2","e2")Option 2:
Code:
=SUBSTITUTE(SUBSTITUTE(A2,"1994","2004"),"d2","e2")Come cells may contain 2 or 3 values of the same to be replaced with the same value and some cells may have 3 separate values that need to be replaced with 3 separate values.
FYI I am using Google Sheets and not Excel.
Last edited by a moderator:
