[Solved] If cell contains x, add y to a different cell.

[ev888]

I need to updated about 2500 products in a CSV. If any cell in a column contains the word 'Boy' (lets just call this Column E), I need to add 'Boy' to all the cells in Column D. Note that it doesn't have to be just the single word 'Boy'; if it appears anywhere in column E (such as Infant Boy, Junior Boy, etc) I need to add 'Boy' to column D. What would be the correct formula for this? I found a similar thread but it was a bit more complex, and I'm not familiar with cell formulas.

[robleyd]

Spreadsheets can either have values or formulae in a cell, not both. So if D already contains data, what you are asking can't be done directly.

You would need to use a helper column containing the formula. Something along the lines of 'IF E contains boy THEN this cell = the value in D with boy appended; ELSE this cell = the value in D'. Depending on exactly what you need, you may have to manipulate the results.

If D is empty, then it can be done directly in D. In either case the exact formula will depend on several things.
1. Is the value you are matching case insensitive?
2. If appending to D, is there any other text needed, e.g. a space separating the appended data?
3. Is the appended data to be exactly the same as what is found in respect of case?
4. Is the search value always going to be Boy?

Possibly there are other things I've not considered!

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples
[Tutorial] Absolute, relative and mixed references

[ev888]

Here is the spreadsheet itself.



There is nothing in column D, nor is 'boy' case sensitive. No spaces are needed. The search value is not always going to be 'boy', I will need to do one for 'girl' as well.

[Zizi64]

Here is the spreadsheet itself.

It is a picture only. Please opload your ODF type sample file here.

[Lupp]

If any cell in a column contains the word 'Boy' (lets just call this Column E), I need to add 'Boy' to all the cells in Column D.

I can't believe you actually mean this. It surely must be done per row.

Code: Select all   Expand viewCollapse view

=CHOOSE(1+ISNUMBER(FIND("boy";LOWER(E2)))+2*ISNUMBER(FIND("girl";LOWER(E2)));"";"Boy";"Girl";"Boy,Girl")

entered into D2 and filled down as far as needed should do.