Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Concatenating a 'New Line' character (Excel 2000)

    Hi All, this is probably very simple ...
    I want to concatenate the contents of two cells, but I wish to have a new line between them. If I were typing it, I would enter a "Alt + Enter". So what would the formula look like?
    =concatenate(F5,???,G5)

    Thanks again

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating a 'New Line' character (Excel 2000)

    Hi,
    You would use:
    =concatenate(F5,char(10),G5)
    and I think you'll also need to format the cell to Wrap Text for it to work.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Concatenating a 'New Line' character (Excel 2000)

    =CANCATENATE(F5,CHAR(10),G5)

    OR

    = F5 & CHAR(10) & G5.

    You should make sure word wrap is set on.

    Andrew C

  4. #4
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating a 'New Line' character (Excel 2000)

    Thanks, Rory, that did work!
    One more thing (don't we all say that!) ...

    =concatenate(F5,char(10),G5)

    How do I make the F5 Bold and the G5 part not Bold?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Concatenating a 'New Line' character (Excel 2000)

    I'm afraid I don't know of any way to do that. If anyone else does, I'd love to know too!
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Concatenating a 'New Line' character (Excel 2000)

    The only way you could do that would be to copy and paste values. You could then select the part for F5 in the formula bar and format as bold. Howeevr that will loose you your formula, unless you keepi it hidden and copy the values to a new column or row.

    Andrew C

  7. #7
    Lounger
    Join Date
    Jun 2002
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating a 'New Line' character (Excel 2000)

    Thanks, Andrew and Rory, ... I need the formula so I'll live without or work-around the Bold issue.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Concatenating a 'New Line' character (Excel 2000)

    You can also use an ampersand:<pre>=F5 & CHAR(10) & G5</pre>

    but you do have to use the Format, Cells, Alignment tab and check the Wrap Text box. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Concatenating a 'New Line' character (Excel 2000)

    Hi Cat,

    Try this:

    <pre>Sub Better()

    Dim C As Range

    Set C = Range("A1")

    With C
    .FormulaR1C1 = "Hay" & vbLf & "You"
    .Characters(Start:=1, Length:=3).Font.FontStyle = "Bold"
    .Characters(Start:=4, Length:=4).Font.FontStyle = "Regular"
    End With

    End Sub
    </pre>


    Apparently, the second .Characters line is necessary, even though the first one should only affect the first three characters.

    It is important to turn Wrap Text on, otherwise the LF character appears as a square and does not behave like a line feed.

    Also, you can substitute vbLf for Chr(10) (vbLf is the VB constant for Chr(10), which is a line-feed. It's a little more "readable".

    Anyway, hope this gives you some ideas.

Posting Permissions

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