Page 1 of 1
[Solved] If cell contains x, add y to a different cell.
Posted: Sun Dec 09, 2018 4:41 am
by 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.
Re: If cell contains x, add y to a different cell.
Posted: Sun Dec 09, 2018 8:19 am
by 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
Re: If cell contains x, add y to a different cell.
Posted: Sun Dec 09, 2018 3:42 pm
by 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.
Re: If cell contains x, add y to a different cell.
Posted: Sun Dec 09, 2018 4:29 pm
by Zizi64
Here is the spreadsheet itself.
It is a picture only. Please opload your ODF type sample file here.
Re: If cell contains x, add y to a different cell.
Posted: Sun Dec 09, 2018 5:31 pm
by Lupp
ev888 wrote: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
=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.