CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulagoogle-sheets-api
Manraj Sandhu
Manraj Sandhu

Reputation: 33

How to substitute text within a string for text in another cell google sheets?

Thank you in advance for any help provided as it's much appreciated! Hope you're all keeping well in these uncertain times.

I have a question regarding Google Sheets. Below you'll find a link to a very large Google Sheets document in which I am trying to substitute text such as 'XXXXX', 'BBBBB', 'TTTTT', 'YYYYY', 'RRRRR' and replacing the same phrases into situations later in the document. If someone could help input a formula into columns such as P, Q, R, S, T, V and W that would be really helpful. I believe I have made the document pretty self-explanatory where in row 1 you can find the labels 'XXXXX', 'BBBBB', 'TTTTT', 'YYYYY', 'RRRRR' and the places to replace them respectively in columns P, Q, R, S, T, V and W.

I have tried to use an arrayformula with substitute, but every time I did this I could only change the first row and it would not cascade down the sheet to affect the other 900+ rows no matter what I tried.

I would appreciate an explanation of how to do it if someone is changing the document so I can learn moving forwards!

The other thing which I am very confused about how to achieve is, if in column O the word 'Yes' appears, then I would like 'VF' to come into column W but if it says 'No' in column O then I don't want it to appear in column W.

Again an explanation of how this is achieved, as well as implementing it into my document would be much appreciated!

Thank you.

https://docs.google.com/spreadsheets/d/1JgAFxqJqVlg2Q-LtLP1udrdJksJAtpC1tLH5dyxf1SA/edit?usp=sharing

Upvotes: 0

Views: 688

Answers (1)

Broly
Broly

Reputation: 921

You can use Arrayformula and combine the text using &. For example in the cell P4 you can have the formula:

=ArrayFormula("How can I buy "&F4:F&" at a trade price? In order to enquire about purchasing "&F4:F&" at a trade price, please get in touch with us at [email protected]")

So you are basically just adding text to F4:F using &. You can do this to all the other columns accordingly.

And using the same logic you can add the following formula in cell W4:

=ArrayFormula(F4:F&", "&J4:J&", "&M4:M&", "&IF(O4:O="Yes", "VF", ""))

Upvotes: 2

Related Questions