Solved

If statements in excel

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

785 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