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

Discuss the spreadsheet application
Post Reply
ev888
Posts: 2
Joined: Sun Dec 09, 2018 4:30 am

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

Post 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.
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
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: If cell contains x, add y to a different cell.

Post 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
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
ev888
Posts: 2
Joined: Sun Dec 09, 2018 4:30 am

Re: If cell contains x, add y to a different cell.

Post by ev888 »

Here is the spreadsheet itself.

Image

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: If cell contains x, add y to a different cell.

Post by Zizi64 »

Here is the spreadsheet itself.
It is a picture only. Please opload your ODF type sample file here.
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.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: If cell contains x, add y to a different cell.

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply