none
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 Mike Walsh FIN Monday, December 14, 2009 9:02 AM (my) spelling
    Monday, December 14, 2009 2:45 AM

Answers

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

    Hope this answers?

    Cheers

    Geoff
    • Edited by Geoff EvelynMVP 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