Can keyboard functions be inserted into a formula?

By cprevost ·
I received a spreadsheet with contractor names in column A and contract #'s in column B. I want to concatenate the cells from columns A & B into column C so that both pieces of information appear in one cell.

The data in both columns A and B vary in lengh.

Here's the puzzle: I want the cells to wrap so that the contractor name appears in the top of the cell and the contract number appears below it.

I could manually go into each cell of column C, position the cursor between the two pieces of information and press ALT + ENTER to obtain the desired result.

There are hundreds of entries on this spreadsheet, and I'd like to know if there is a way for a formula to do this for me.

The existing formula looks like:
=A1&" "&B1

What would I need to type after the quotation marks to make the data data in B1 appear on the next line within the same cell (C1)?

I'm using Excel 2003.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

I'm sorry, but...

by TobiF In reply to Can keyboard functions be ...

I entered this special character in a cell and then used CODE to check the value, which is 10.
Now, when I enter =A1&CHAR(10)&A2 this special character becomes a part of the string, but I get no line break.
Same happens when I enter only the new line character in a different cell and concatenate the value of three cells together.

Next, I recorded a macro. It came out this way:
activecell.formular1c1="Test" & Chr(10) & "Line2"

Again, the same character code. But, when I run this macro, I get a two line entry...

Next, So I try creating the whole string in VBA:
Function TwoLiner(Arg1 As String, Arg2 As String) As String
TwoLiner = Arg1 & Chr(10) & Arg2
End Function

However, even this won't give me a line break.

Now I try a thing that really "should" work:
I enter a(newline)b in one cell, and then simply link another cell to this one. And even in this case, I won't get the line feed!

So, it seems you can't get the line break in a dynamic text.

The only workaround I could find was to write a macro that would walk through your list and, cell by cell, entered the needed value, picking the values from the source cells.

Sub CombineTwoLiners()
While ActiveCell.Offset(0, -2).Formula <> ""
ActiveCell.FormulaR1C1 = _
ActiveCell.Offset(0, -2).Formula & _
Chr(10) & ActiveCell.Offset(0, -1).Formula
ActiveCell.Offset(1, 0).Select

End Sub

Related Discussions

Related Forums