Solved

If statements in excel

Posted on 1999-01-13
4
697 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
[X]
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
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

752 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