| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
concatination of cell contents
pls help with a simple query.
I need to concatinate the contents of two cells into a third cell. For example, if cell A1 = "john" and cell B1 = "01.01.2006", I need cell C3 to contain "john 01.01.2006". What kind of formula should cell C3 contain? thanks in advance |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
A1&" " &B1
space between two apostrophes "archeti" wrote in message ... pls help with a simple query. I need to concatinate the contents of two cells into a third cell. For example, if cell A1 = "john" and cell B1 = "01.01.2006", I need cell C3 to contain "john 01.01.2006". What kind of formula should cell C3 contain? thanks in advance |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
Hi Venkat try this formula in C1 = concatenate(a1," ",b1) -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php...o&userid=17590 View this thread: http://www.excelforum.com/showthread...hreadid=482731 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
thanks to all
"NITESH G" wrote: Hi Venkat try this formula in C1 = concatenate(a1," ",b1) -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php...o&userid=17590 View this thread: http://www.excelforum.com/showthread...hreadid=482731 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
Dear All,
both versions of concatenation work BUT, in the case of the example specified, that is: "john" in cell A1 "01.01.2006" in cell B1, the date gets converted into an integer such that the end result in cell C1 reads: "john 123456", where '123456' represents an integer by which Excel repesents the date! any ideas of how to make the date appear in the form dd.mm.yyyy? I've tried customising cell C1 accordingly but so far I did not manage. pls help thanks "NITESH G" wrote: Hi Venkat try this formula in C1 = concatenate(a1," ",b1) -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php...o&userid=17590 View this thread: http://www.excelforum.com/showthread...hreadid=482731 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
Hey, This formula = =CONCATENATE(A1," ",B1) is the best solution. Incase u cannot see the date, then , righ click on the resultant cel and select format cells and select text or keep it default as general. Try it out. archeti Wrote: Dear All, both versions of concatenation work BUT, in the case of the example specified, that is: "john" in cell A1 "01.01.2006" in cell B1, the date gets converted into an integer such that the end result i cell C1 reads: "john 123456", where '123456' represents an integer by which Exce repesents the date! any ideas of how to make the date appear in the form dd.mm.yyyy? I'v tried customising cell C1 accordingly but so far I did not manage. pls help thanks "NITESH G" wrote: Hi Venkat try this formula in C1 = concatenate(a1," ",b1) -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile http://www.excelforum.com/member.php...o&userid=17590 View this thread http://www.excelforum.com/showthread...hreadid=482731 -- dp ----------------------------------------------------------------------- dpt's Profile: http://www.excelforum.com/member.php...fo&userid=2586 View this thread: http://www.excelforum.com/showthread.php?threadid=48273 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
the result does work, but somewhat is dependant on the date format in cell B1.
date formats dd/mm/yyyy and dd.mm.yyyy will still result in an integer showing in cell C1, even with a text/general formatting in cell C1 the requested result does show however, if a date "dd.mm.yyyy" is directly inserted in cell B1. I wonder what am I missing here. Maybe I need insert a validation mask in cell B1 for the user to input a date in the format required, but I still think it can be sorted out more professionally! archeti "dpt" wrote: Hey, This formula = =CONCATENATE(A1," ",B1) is the best solution. Incase u cannot see the date, then , righ click on the resultant cell and select format cells and select text or keep it default as general. Try it out. archeti Wrote: Dear All, both versions of concatenation work BUT, in the case of the example specified, that is: "john" in cell A1 "01.01.2006" in cell B1, the date gets converted into an integer such that the end result in cell C1 reads: "john 123456", where '123456' represents an integer by which Excel repesents the date! any ideas of how to make the date appear in the form dd.mm.yyyy? I've tried customising cell C1 accordingly but so far I did not manage. pls help thanks "NITESH G" wrote: Hi Venkat try this formula in C1 = concatenate(a1," ",b1) -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php...o&userid=17590 View this thread: http://www.excelforum.com/showthread...hreadid=482731 -- dpt ------------------------------------------------------------------------ dpt's Profile: http://www.excelforum.com/member.php...o&userid=25861 View this thread: http://www.excelforum.com/showthread...hreadid=482731 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
Does anyone know if it is possible to Un-concatenate a cell. If "John Smith" is in cell A2 can i put "John" in cell B2 and "Smith in cell C2 -- Shak ----------------------------------------------------------------------- Shake's Profile: http://www.excelforum.com/member.php...fo&userid=2857 View this thread: http://www.excelforum.com/showthread.php?threadid=48273 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
concatination of cell contents
use data(menu)-text to columns
delimiter is <space in the wizard in the thrid step denote destination as B2 "Shake" wrote in message ... Does anyone know if it is possible to Un-concatenate a cell. If "John Smith" is in cell A2 can i put "John" in cell B2 and "Smith" in cell C2. -- Shake ------------------------------------------------------------------------ Shake's Profile: http://www.excelforum.com/member.php...o&userid=28577 View this thread: http://www.excelforum.com/showthread...hreadid=482731 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| concatenate contents of cells whose contents resemble cell referem | Excel Worksheet Functions | |||
| Conditional Concatination | Excel Discussion (Misc queries) | |||
| Problem with concatination result. Last digit change to 0 | Excel Worksheet Functions | |||
| How set a format on one of more strings in a concatination | Excel Discussion (Misc queries) | |||
| Excal Concatination of cells that are not empty | Excel Worksheet Functions | |||
Linear Mode
