combining text and formulas

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe

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

or

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

Lee.

Board Regular
Joined
Dec 15, 2012
Messages
170
Code:
=C14&" - "&C15&" - "&C16
That does work, make sure you're copying it exactly
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
How are the cells supposed to be formatted?
 

andrewb90

Well-known Member
Joined
Dec 16, 2009
Messages
1,067
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.

Board Regular
Joined
Dec 15, 2012
Messages
170
Could use something like this to remove the #value!
=If(Iserror(C14&" - "&C15&" - "&C16)=true,"Data Missing",C14&" - "&C15&" - "&C16)
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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.
 

Forum statistics

Threads
1,081,727
Messages
5,360,913
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top