Answered by:
How to concatenate calculated columns?

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 =
244587
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:
24450
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 Mike Walsh FIN Monday, December 14, 2009 9:02 AM (my) spelling
Question
Answers

I figured it out (to an extent). I modified my formula to be:
=CONCATENATE(Col1,"",Col2,"",Col3)
Now the result is:
24450
Just FYI. Marked as answer by GuYumingMicrosoft contingent staff, Moderator Tuesday, December 15, 2009 3:38 AM
All replies

I figured it out (to an extent). I modified my formula to be:
=CONCATENATE(Col1,"",Col2,"",Col3)
Now the result is:
24450
Just FYI. Marked as answer by GuYumingMicrosoft contingent staff, Moderator Tuesday, December 15, 2009 3:38 AM

Hi there Tim,
Try this:
=IF(Col11=1,"00",Col1)&""&IF(Col21=1,"00",Col2)&""&IF(Col31=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/enus/sharepointtechnology/HA011609471033.aspx
Hope this answers?
Cheers
Geoff Edited by Geoff EvelynMVP Monday, December 14, 2009 8:46 AM Forgot the link!

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 19, 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 
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(Col11=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(Col11=1,"00",IF(Col1<10,"0"&Col1,Col1)) & "" & IF(Col21=1,"00",IF(Col2<10,"0"&Col2,Col2)) & "" & IF(Col31=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 

check this calculated columns formulas referrence
http://blog.raminassar.com/2011/11/13/examplesofusingcalculatedcolumnformulas/
Regards,
Rami M. Nassar
(MCP, MCTS, MCPD)
My Blog
EMail
Don't forget to click Mark as Answer on the post that helped you.