Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine 2 text cells into 1 (xls 97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    =A1&", "&A2
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    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

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    Why not in two cells one underneath the other?

    Curious
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

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

    does that help ?

  6. #6
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

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

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    Sir, don't you need a second ampersand, or do you have something new to teach me?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    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

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    John,

    Yes, a second ampersand would be useful.

    Perhaps there is much you can teach me.

    Andrew

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    Andrew, in case you took it that way, I did not mean to be sarcastic. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Regards,
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    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

  14. #14
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    I have thought about this "feature" <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15> (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 ....
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Combine 2 text cells into 1 (xls 97)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •