Combine 2 text cells into 1 (xls 97)

Topic

New Reply

Howdy all,

I’d like to reference a cell on sheet1 to be equal to cell A1 and A2. The value of those cells contain last names. I want my cell on sheet 1 to display the references as Smith, Jones. How do I reference a cell to display multiple text values, thanks.

Ed

Reply | Quote

Replies

=A1&”, “&A2

Reply | Quote

Ok that works fine but, I need the 2nd cell to be placed under the first one. When i use ALT + enter the formula stops calculating and displays what is exactly in the cell. (=A1 & “,” B1). How can i make it look like this: Smith
Jones

without losing my formula, thanks.

Ed

Reply | Quote

Why not in two cells one underneath the other?

Curious

Reply | Quote

The sheet that these cells are going into is getting its data from a worksheet where project info is being entered. The cells that I am combining are last names of those responsible for a project. If I place their names in separate cells then Sheet 1 will be extremly long because up to 6 people can be responsible for one project. Also, Sheet 1 is linked to a Word Document Table and each project lies on 1 row with all responsible members on it. To make a project 4 lines long to cover 4 names is too bulky. I hope you get what I’m trying to say. Its a size and format issue mostly, but it was a good idea.

Reply | Quote

Do you mean Format Cell, Alignment, Wrap Text?

Edited
How come you are using Alt + Enter? Never mind, you are using in the middle of the cell to force the wrap.

Try =A1&”,”&CHAR(10)&A2 and autofit the rows.

Reply | Quote

JOHN YOU DA MAN!!!!!!!!!!! How the heck do you guys know all this stuff, your knowledge boggles me and saves me. Thanks

PS – the 2nd & was just a typo.

ED

Reply | Quote

The formula should read as =(A1 & “,” B1), instead of (=A1 & “,” B1), or just plain =A1 & “,” B1.

does that help ?

Reply | Quote

It is being entered as just plain =A1 & “,” B1. The ( ) where used to show what was acually being displayed.

Reply | Quote

Sir, don’t you need a second ampersand, or do you have something new to teach me?

Reply | Quote

John,

Yes, a second ampersand would be useful.

Perhaps there is much you can teach me.

Andrew

Reply | Quote

Andrew, in case you took it that way, I did not mean to be sarcastic.

I was reading a Walkenbach book yesterday which explained how Excel 2000 will do “intersection” (?) calculations, so that if you have a table with column headings such as “Q1”, “Q2”, etc., and row headings such as “Div1”, “Div2”, you can enter “= Q2 Div1” and it will return the intersection value without the need for named ranges. I thought maybe you were doing some similar trick. BTW Walk. advises against using intersection fomulas because they are hard to trace and easy to confuse with named ranges. Since I don’t yet use XL2k I have had no opportunity to play with this feature.

Regards,

Reply | Quote

John,

I did not take it that way at all, and did not feel you were sarcastic. Now I hope I did not give the impression that i was (to borrow your expression) grouchy, and I was not sarcastic either.

Sorry no fancy J Walkenbach tricks spring to mind in this case. Actually I think that Intersection operator works in XL97. If you name your coulmns Div1, Div2 etc , and your Rows as Qtr1, Qtr2 (Q1 would not be valid as it is the address of a cell) etc, then you can enter Div1 Qtr2, to get the value for Division 1 in the 2nd Quarter. If you have XL97 try it out. It can be convenient in formulas, and if you use meaningful names it should helpful. I have not really tried in Xl2000, and did not realise it could be used without naming the range.

Andrew

Reply | Quote

I have thought about this “feature” (to borrow Shania Twain’s line – “it don’t impress me much”) for a while now – and in order to make this work XL must be creating ranges – automatically. That makes me nervous – what if you had other ranges in the doc, with the same name? Wouldn’t it interfere or just not work, just when you expected it to?

Sashaying off into the sunset in a leopard skin coat ….

Reply | Quote

Catherine, I agree. But in XL97 I think that you must name the ranges yourself before you can use that type of operator (the space).

I never bother with it anyway, as it is just a curiosity.

Andrew

Reply | Quote

Sir, you are right on both counts. The feature -was- introduced in XL97, and using “Q1”, etc., would be invalid.

I have been crazy busy at work and have been getting very terse. Hence my concern that I may have offended you. Of course I have offended most everyone who reads “Scuttlebutt”, so it’s nothing new, but then I was outdone by some really awful British puns..

Reply | Quote