| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add 0 before month 1 to 9?
Hello,
I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
insert a "helper" column by the month and make it =RIGHT("0" & B2,2) and that
will pad the month. "Cam" wrote: Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
=IF(LEN(B1)=1,A1&","&" "&0&B1,A1&","&" "&B1)
hope this helps. "Cam" wrote in message ... Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
If the Year is in column A and the Month is in column B, try in column C:
=A2&", "&IF(B2<10,"0"&B2,B2) Hope this helps, Hutch "Cam" wrote: Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
another way to do it as
just select the cells, and go to format, custom and type 00, press ok that should work too. "Cam" wrote: Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
try
=A2&", "&RIGHT("0" &B2,2) Mike "Cam" wrote: Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
=A2&", "&TEXT(B2,"00")
-- David Biddulph "Cam" wrote in message ... Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
=a2&", "&text(b2,"00")
or =text(date(A2,B2,1),"yyyy, mm") Cam wrote: Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add 0 before month 1 to 9?
Another possibility
In C2 copied down =IF(B29,A2 & ", " & B2,A2 & ", " & "0" & B2) "Cam" wrote in message ... Hello, I am not sure how to use the formula or function that CONCATENATE two fields based on a IF function. If the month field is 1 to 9, then add 0 before the month, then concatenate the year & month. Example Data: Year Month Concatenate Result 2007 3 2007, 03 2007 4 2007, 04 2008 12 2008, 12 2008 6 2008, 06 2009 10 2009, 10 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel to make the days cary over month to month automaticly | New Users to Excel | |||
| Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
| Number of days in month counted from shortened name of month & yea | Excel Worksheet Functions | |||
| Retrieve data for previous 3, 6, 12 month given current month | Excel Worksheet Functions | |||
| transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) | |||
Linear Mode
