MrExcel Publishing
Your One Stop for Excel Tips & Solutions

combine 2 cells text into one cell as a whole text


Posted by gan on August 29, 2001 12:02 AM

hi,
i need some helps in how to..combine for intance :


A B C
JAN 2001 JAN-2001
FEB 2001 FEB-2001

where text from column A merge with B to have outcome of column C.
thanks for the help


Posted by gan on August 29, 2001 12:04 AM


[pre] A B C JAN 2001 JAN-2001 FEB 2001 FEB-2001
[/pre] thanks for the help

Posted by enter in C1 =A1-B1 on August 29, 2001 12:10 AM

Posted by Aladin Akyurek on August 29, 2001 12:11 AM


In C1 enter: =A1&"-"B1

Copy down as far as needed.

======================= : i need some helps in how to..combine for intance : [pre]

Posted by Gyula on August 29, 2001 12:12 AM

A B C JAN 2001 JAN-2001 FEB 2001 FEB-2001 thanks for the help

How are columns A & B formatted? As Dates, or as Text?
Do you need Column C formatted as Dates or as Text?


Posted by enter in C1 =A1-B1 - hmm. seems you can't use ampersands and double quotes in the name field on August 29, 2001 12:15 AM

Posted by Josef on August 29, 2001 12:19 AM


The formula you are trying to get is :-
C1= A1 & "-" & B1

But this will only work if A1 and B1 are formatted as general or text, and not as dates.
See Gyula's request for more info.

Posted by not meaning to argue, but ... on August 29, 2001 12:32 AM

since "MMM" and "YYYY" are not among the available selection of date formats (in XL97 anyway*)wouldn't "JAN" and "2001" have to be general, text, custom, etc format, but in any case not a date format, so the extra info on format is not required?

* if later versions of excel do have these date formats I apologise and concede the point.

Posted by Zoltan on August 29, 2001 12:42 AM

* if later versions of excel do have these date formats I apologise and concede the point.


As far as I know, dates CAN be formatted as "mmm" and "yyy" in Excel 97 (and later versions) by using Custom format.

Posted by Aladin Akyurek on August 29, 2001 1:07 AM

Storm in a teacup?

Gan says having

A1 --> JAN
B1 --> 2001

& wants

C1 --> JAN-2001

It wouldn't make any sense having e.g.,

A1 --> 01-01-2001, custom-formatted as "mmm" to obtain "Jan", which has been set somehow in uppercase; and

B1 --> 01-01-2001, (or another date, for that matter) custom-formatted as "yyyy" to obtain 2001,

would it?

It's most plausible that A1 contains the text JAN & B1 text or number-formatted 2001.

The formula in C1 becomes on this interpretation simply:

=A1&"-"&B1

or equivalently:

=CONCATENATE(A1,"-",B1)

Cheers.

Posted by Anon on August 29, 2001 2:21 AM

Most unlike you to be so presumptive !


Posted by Aladin Akyurek on August 29, 2001 5:08 AM

Anon: I don't believe...

I was or intending to be "presumptive." I should have better used a smiley instead of a question mark on the subject line.

Aladin

=================

Posted by Eric on August 29, 2001 1:40 PM

make that =a1&"-"&B1

:-) In C1 enter: =A1&"-"B1 Copy down as far as needed. =======================

Posted by : ) to josef, zoltan, anon, aladin and anyone else who's looking... : ) on August 29, 2001 4:48 PM

Re: Anon: I don't believe...


I posted the original "not meaning to argue, but..." in reply to Josef.
Yes, as Zoltan says, cell entries that look like dates (eg JAN, 2001, JAN-2001, etc) can be formatted to whatever you like using a custom format, but if you do use a custom format it's no longer a date format, which was precisely my point - if you are using excel's custom format, you cannot be using excel's date format, which means that excel will not think it's a date, and therefore the formula =A1&"-"&B1 will work.
I hope you all have a nice day. )

Posted by Jackie on August 29, 2001 6:37 PM

Sorry, but you are absolutely incorrect .....

I posted the original "not meaning to argue, but..." in reply to Josef. Yes, as Zoltan says, cell entries that look like dates (eg JAN, 2001, JAN-2001, etc) can be formatted to whatever you like using a custom format, but if you do use a custom format it's no longer a date format, which was precisely my point - if you are using excel's custom format, you cannot be using excel's date format, which means that excel will not think it's a date, and therefore the formula =A1&"-"&B1 will work. I hope you all have a nice day. : )


Together with all the other people you mentioned, I'm also listening.

Please note that you CAN have a cell formatted as a date that shows "mmm" or "yyyy". All it would be is a custom formatted date.
Try it and you'll see. Format a cell as date and then custom format it.
Custom format is not a category on its own. It is just a user defined format of one of the main categories (text,date,number,etc.)

Posted by Yes, I am absolutely incorrect . I apologise and concede the point. : ) on August 29, 2001 8:18 PM

Re: Sorry, but you are absolutely incorrect .....