IF Function and Concatenation

L

Lance Hebert

I have three columns that I am trying to Concatenate. I only need to combine
two columns at a time, but I want to be able to Concatenate say Column C and
Column A if Column D=4, 5 or 6 or if Column D=11, 12 or 13 than Column C and
Column B would be Conceatenated.

Is this possible? I know how to put in the IF function, but can the IF
function have a number of possibilites in it?

Thanks so much for all of your help.
 
J

Joe User

Lance Hebert said:
I want to be able to Concatenate say Column C and
Column A if Column D=4, 5 or 6 or if Column D=11, 12 or 13 than
Column C and Column B would be Conceatenated.

Is this what you want:

=if(OR(D1={4,5,6}), C1 & A1, if(OR(D1={11,12,13}), C1 & B1, ""))

If D1 can contain __only__ one of the values 4, 5, 6, 11, 12 or 13, that can
be simplified:

=if(OR(D1={4,5,6}), C1 & A1, C1 & B1)

or

=C1 & if(OR(D1={4,5,6}, A1, B1)

Also, if D1 is text, not numeric, you need to write OR(D1={"4","5","6"}) and
OR(D1={"11","12","13"}).


----- original message -----
 
D

David Biddulph

Sorry, you said columns C and A, so:

=IF(OR(D1={4,5,6}),C1&A1,IF(OR(D1={11,12,13}),C1&B1,""))
or perhaps
=C1&IF(OR(D1={4,5,6}),A1,IF(OR(D1={11,12,13}),B1,""))
depending on what you want if D1 isn't one of those specified values.
 
L

Lance Hebert

This is what I have used:
=IF(OR(L2={37,38}),M2&K2,L2&K2)

but if L2 = 37 or 38, the result (which should be M2&K2) is actually the
L2&K2 result. What would I have done wrong?
 
M

Max

L2 probably contains text numbers, that's why
Switch it all to be text number comparisons,
Try: =IF(OR(L2&""={"37","38"}),M2&K2,L2&K2)
The above should now work fine regardless whether L2 contains real or text
nums. Success? hit the YES below
 
J

Joe User

Lance Hebert said:
This is what I have used:
=IF(OR(L2={37,38}),M2&K2,L2&K2)
but if L2 = 37 or 38, the result (which should be M2&K2) is actually the
L2&K2 result. What would I have done wrong?
 
J

Joe User

Lance Hebert said:
This is what I have used:
=IF(OR(L2={37,38}),M2&K2,L2&K2)
but if L2 = 37 or 38, the result (which should be M2&K2) is actually
the L2&K2 result. What would I have done wrong?

If L2 is text, you should write OR(L2={"37","38"}), as I mentioned before.

But perhaps L2 is numeric and only __appears__ to be 37 or 38 due to
formatting. In other words, the actual numeric value might be something
other than exactly 37 or 38.

One way to see this is to go through steps to format the cell as Scientific
with 14 decimal places (click on Format > Cells > Number tab). Even if you
do not understand the format (x.xx...xxE-xx), this will usually demonstrate
whether or not the cell value is exactly an integer (x.00...00E+xx).

The following might solve your problem:

=IF(OR(ROUND(L2,0)={37,38}), M2&K2, L2&K2)

But even that will not work if the problem is, for example: L2 contains
time, and you formatted the cell to display only hours or minutes.


----- original message -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top