Solved

If statements in excel

Posted on 1999-01-13
4
696 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
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 …

679 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