Software

Ask something
Question Locked

Can keyboard functions be inserted into a formula?

0 helpful
2 replies

by cprevost

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

2 replies

by tobif

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
Wend

End Sub

This discussion is closed to new replies.