Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

If statements in excel

Posted on 1999-01-13
4
695 Views
Last Modified: 2008-03-03
I want to concatenate two text cells, but only "if" cell X does not "contain" cell Y.  Cell X is the text string with spaces between words - no puntuation.  No spaces in text cell Y.
Example:
text cell X = cat dog rain
text cell Y = cat
Concatenation yields result:  cat dog rain  (X contains Y)
Example 2:
text cell X = fish blue thunder
text cell Y = cat
Concatenation yields result:  fish blue thunder cat (X does not contain Y
I guess I am looking for an "If contains" statement or function.  Maybe there are a few functions to perform.
0
Comment
Question by:toddweaver
  • 2
  • 2
4 Comments
 

Author Comment

by:toddweaver
ID: 1615631
Edited text of question
0
 
LVL 1

Accepted Solution

by:
Pandora earned 70 total points
ID: 1615632
Hi Todd
if in A2 you have the value
Cat
and in A3 you have the value Cat Dog
the result of the formula will be equal to A3 ie
Cat Dog
For any other value the 2 will be concatenated with a space between
=IF(FIND(A2,A3,1)>0,A3,CONCATENATE(A2," ",A3))
The statement reads if the *exact* text in A2 exists in a3, the result is A3 optherwise add A2 + <a space> + A3
Hope this helps you
P.
0
 

Author Comment

by:toddweaver
ID: 1615633
Excel changed my formula from what I typed to what you see below.
It seems to have worked.  I just need to tinker a bit to get my desired result, for instance I want it to print B1 concatenated with A1 especially if it does not contain A1.  So, I think I am close.  It could be that my brain is off at this time in the evening.  Where can I find formulas like the one you gave me?
=CONCATENATE(IF(FIND(A1,B1,1)>0,B1,CONCATENATE(A1," ",B1)))
cat      mouse cat      mouse cat
cat      mouse cat      mouse cat
cat      mouse      #VALUE!
dog      dog      dog
dog      dog      dog
dog      dog      dog
mouse      mouse      mouse
mouse      mouse      mouse
mouse      blue      #VALUE!

0
 
LVL 1

Expert Comment

by:Pandora
ID: 1615634
It should not need the additional concatenate statement (it is simply adding itself to nothing, which is why it still works); the best place to find formulae is usind the f(x) toolbar button next to the sigma button; the functions are listed in groups and the ones you are interested in are:
logical - if statements
text - find, concatenate
the expression builder will tell you what arguments go where, and what they do.  select a blank cell and then click {fx} and have a play - build the statements one at a time from the inside out (this is called 'nesting') and when you're happy each stage works, build the next one inside it - good luck & happy hunting!
P.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question