Concatenating a ‘New Line’ character (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi Cat,

Try this:

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

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.

Reply | Quote

You can also use an ampersand:

=F5 & CHAR(10) & G5

but you do have to use the Format, Cells, Alignment tab and check the Wrap Text box. –Sam

Reply | Quote

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

OR

= F5 & CHAR(10) & G5.

You should make sure word wrap is set on.

Andrew C

Reply | Quote