# How to concatenate calculated columns?

### Question

• Hello,

I have a calculated column in which I am concatenating a group of other calculated columns to produce a string of values.

For instance I have 3 Calculated columns:
Col1
Col2
Col3

I have an additional calculated column that creates the string value:
Col4

The current Formula that I have for Col4 is:
=CONCATENATE(Col1,-Col2,-Col3)

So if my columns =
Col1=24
Col2=45
Col3=87

Then Col4 should =
24-45-87

This works just fine with one exception.  If any of the above columns equals "0" then the concatenation breaksdown.  For instance if Col3=0 then the result is:
24-450

So what I did was to put 00 in the last line of my IF statement for the false statement for Col3 like so:
=IF[FAC]="Finance",65,"00")

This did not work.

Can someone make a suggestion on how I can get the string to give me a "-" and two '00's at the end if the value is null?

Thank you so much for any help you may be able to provide.

Sincerely,
Tim
• Edited by Monday, December 14, 2009 9:02 AM (my) spelling
Monday, December 14, 2009 2:45 AM

• I figured it out (to an extent).  I modified my formula to be:

=CONCATENATE(Col1,"-",Col2,"-",Col3)

Now the result is:
24-45-0

Just FYI.
Monday, December 14, 2009 3:15 AM

### All replies

• I figured it out (to an extent).  I modified my formula to be:

=CONCATENATE(Col1,"-",Col2,"-",Col3)

Now the result is:
24-45-0

Just FYI.
Monday, December 14, 2009 3:15 AM
• Hi there Tim,

Try this:

=IF(Col1-1=-1,"00",Col1)&"-"&IF(Col2-1=-1,"00",Col2)&"-"&IF(Col3-1=-1,"00",Col3)

Basically, each Col is a number column, so using the IF statement to find out if any of them is less than 0 by subtracting 1 against them, and to display a 00 if thats the case, else display the relevant value and instead of using CONCENTATE using ampersands to separate with the dashes.

For more information on excel formulas a good place to check out is here:

http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

Cheers

Geoff
• Edited by Monday, December 14, 2009 8:46 AM Forgot the link!
Monday, December 14, 2009 8:45 AM
• Hey Geoff,

That worked great!  Thanks so much for taking the time to respond.  I wanted to know if there is a similar method for adding a zero in front of a single digit value as well?

For example if a  column is returning a single digit between 1-9, then I would like that column to read:
01
02
03
04
05
06
07
08
09

My confusion is in how many arguments you can put into this statement because on the surface it appears that I would need to include the following line for all above digits into my script in order to write our the value in the correct format:
=IF(Col11=1,"01",Col1)&"-"&IF(Col11=2=2,"02",Col1)&"-"& etc...

Am I on the right track?

Thanks so much.

Sincerely,
Tim
Tuesday, December 15, 2009 12:39 PM
• Hi there Tim,

Mmmmm...

Actually, I would try something like a nested IF on the second statement:

For example:
=IF(Col11=1,"01",Col1)

That says if Col1 =1 then display 01

Here's my modification - assuming the value in Col1 is 8
IF(Col1-1=-1,"00",IF(Col1<10,"0"&Col1,Col1))

That says if Col1 =1 then display 01, else if Col1 <10 then display 08, else display the number because its greater than 10 and therefore does not need to display the 0 in front of it.

Therefore you sould use that statement starting with an  = thus:
IF(Col1-1=-1,"00",IF(Col1<10,"0"&Col1,Col1)) & "-" & IF(Col2-1=-1,"00",IF(Col2<10,"0"&Col2,Col2)) & "-" & IF(Col3-1=-1,"00",IF(Col3<10,"0"&Col3,Col3))

etc

Watch out though for the length of the formula - you may want to rethink format of the colums - but thats a whole new thread!

Hope that helps

Cheers

Geoff
Tuesday, December 15, 2009 1:44 PM
• Hi Geoff

Thanks again for your thoughts on this!  I will  test this out.

Understanding that the Calculated column is limited to 7 statements, is there a size restrcition (as you ellude to ) as well?  I didn't know that.

Thank you again.

Sincerely,
Tim
Tuesday, December 15, 2009 2:20 PM
• check this calculated columns formulas referrence

http://blog.raminassar.com/2011/11/13/examples-of-using-calculated-column-formulas/

Regards,
Rami M. Nassar
(MCP, MCTS, MCPD)
My Blog
E-Mail
Don't forget to click Mark as Answer on the post that helped you.
Sunday, November 13, 2011 9:37 AM