Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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



Check out our Excel Resources

Re: combine 2 cells text into one cell as a whole text

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


Re: combine 2 cells text into one cell as a whole text

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


Re: combine 2 cells text into one cell as a whole text

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]


Re: combine 2 cells text into one cell as a whole text

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?



Re: combine 2 cells text into one cell as a whole 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


Re: combine 2 cells text into one cell as a whole text

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.


Re: combine 2 cells text into one cell as a whole text

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.


Re: combine 2 cells text into one cell as a whole text

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.


Storm in a teacup?

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

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.


Most unlike you to be so presumptive !

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



Anon: I don't believe...

Posted by Aladin Akyurek on August 29, 2001 5:08 AM
I was or intending to be "presumptive." I should have better used a smiley instead of a question mark on the subject line.

Aladin

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


make that =a1&"-"&B1

Posted by Eric on August 29, 2001 1:40 PM
:-) In C1 enter: =A1&"-"B1 Copy down as far as needed. =======================


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

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

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. )

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

Posted by Jackie on August 29, 2001 6:37 PM
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.)


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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.