[Solved] If cell contains x, add y to a different cell.
[Solved] If cell contains x, add y to a different cell.
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.
Last edited by ev888 on Sun Dec 09, 2018 8:17 pm, edited 1 time in total.
OpenOffice 6.0.1.1 (x64) on Windows 10
Re: If cell contains x, add y to a different cell.
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
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
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: If cell contains x, add y to a different cell.
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.
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.
OpenOffice 6.0.1.1 (x64) on Windows 10
Re: If cell contains x, add y to a different cell.
It is a picture only. Please opload your ODF type sample file here.Here is the spreadsheet itself.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: If cell contains x, add y to a different cell.
I can't believe you actually mean this. It surely must be done per row.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.
Code: Select all
=CHOOSE(1+ISNUMBER(FIND("boy";LOWER(E2)))+2*ISNUMBER(FIND("girl";LOWER(E2)));"";"Boy";"Girl";"Boy,Girl")
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München