combining text and formulas

[andrewb90]

Hi,

I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.

Here is the data that I am trying to combine

C14: 2013
C15: Period 6
C16: Week 1

In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"

Is there such a way to do this?

Thanks,

 

[Weazel]

maybe

=C14&" - "&C15&", "&C16

or

=CONCATENATE(C14," - ",C15,", ",C16)

 

[andrewb90]

I keep ending up with #VALUE!

 

[Lee.]

=C14&" - "&C15&" - "&C16

That does work, make sure you're copying it exactly

 

[andrewb90]

How are the cells supposed to be formatted?

 

[Lee.]

Sample document - https://dl.dropboxusercontent.com/u/54352977/andrewb90.xls

 

[andrewb90]

ah, I figured out what was wrong, I had to enter in something on the other cells, the existing text didn't get recognized until I did that.

 

[Lee.]

Could use something like this to remove the #value!
=If(Iserror(C14&" - "&C15&" - "&C16)=true,"Data Missing",C14&" - "&C15&" - "&C16)

 

[Weazel]

just a thought but you could check and see if you have 'transition formula evaluation' turned on which is usually under the Lotus compatability area of advanced options

not sure where it is in 2003 though.

 

[Rick Rothstein]

Could use something like this to remove the #value!
=If(Iserror(C14&" - "&C15&" - "&C16)=true,"Data Missing",C14&" - "&C15&" - "&C16)

You do not have to test the return value from ISERROR to see if it is TRUE or FALSE... those are the values it returns naturally, the the IF function can use them directly. Think about it... you are testing if TRUE=TRUE and, if it does, that evaluation is TRUE (which is what the IF function uses)... but if the return value from ISERROR is TRUE, why test it only to produce the same TRUE value after the test? Just use the TRUE it returns directly. Same analysis for when it returns FALSE.