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:
I have an additional calculated column that creates the string value:
The current Formula that I have for Col4 is:
So if my columns =
Then Col4 should =
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:
So what I did was to put 00 in the last line of my IF statement for the false statement for Col3 like so:
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.
- Edited by Mike Walsh FIN Monday, December 14, 2009 9:02 AM (my) spelling
Hi there Tim,
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:
Hope this answers?
- Edited by Geoff EvelynMVP Monday, December 14, 2009 8:46 AM Forgot the link!
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:
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:
Am I on the right track?
Thanks so much.
Hi there Tim,
Actually, I would try something like a nested IF on the second statement:
That says if Col1 =1 then display 01
Here's my modification - assuming the value in Col1 is 8
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))
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
check this calculated columns formulas referrence
Rami M. Nassar
(MCP, MCTS, MCPD)
Don't forget to click Mark as Answer on the post that helped you.