Excel 'Special' Characters in Expressions

[Guest]

I have a need to be able to create a funtion/expression that can issue a
'hard carriage return & line feed'. On the keyboard this would be 'Alt' +
'Enter'. I want to take two (or more) cells and combine them into one cell
with each value on it's own 'line' in the receiving cell.
============================
Receiving Cell Cell 1 Cell 2
Smith John Smith
John
============================
I am trying to create a Membership Directory for print and distribution. I
do having it working by determining the length of the Sending Cell and then
padding the end with 'n' spaces depending on the size of the Receiving Cell
and the length of the Sending Cell. This works but is rather arbitrary
unless using fixed fonts which is not reasonable.
Could I capture this 'special' character by recording a macro (which I will
try)? Or does someone know what this 'special' character might be?

Thanks for any help you might provide,
DannyDont

 

[Dave Peterson]

In excel:
=a1&char(10)&b1

in VBA:
with activesheet
.range("c1").value = .range("a1").value & vblf & .range("b1").value
end with

alt-enter = char(10) = vblf = chr(10)

These are line feeds.

Carriage returns are char(13), vbCR, or Chr(13). But don't use them in your
cells. You want alt-enters.

 

[Guest]

Well, I answered my own question by recording a Macro. This macro replaced
the 'Alt' + 'Enter' with a Chr(10). Of course this was VB for Excel and
wouldn't you know that the command was different in an Excel Expression.
But, it was an easy find to go from Chr(10) to CHAR(10). Thanks for all your
help. Sometimes it just takes explaining something to others to turn the
light on!!!!

DannyDont

 

[Guest]

Dave,

Thank you very much. As you were providing the answer, I was recording a
Macro and discovering the information that you provided. When using Help in
Excel, I just didn't think about the CHAR worksheet function. Perhaps if I
had asked a question rather than using the index it would have been easier.

Thanks for your RAPID response.

DannyDont

 

[David McRitchie]

What's wrong with just using the Excel worksheet for a directory listing.

Sounds more like you are formatting for printing labels, in which case
you would use Excel as the database and print with MS Word.
http://www.mvps.org/dmcritchie/excel/mailmerg.htm