Excel Question ... sorry about this.

[The Great Sun Jester]

I'm usually pretty good about getting Excel to dance to whatever tune I want to play, but this time I'm stumped and it's a really simple problem.

How do you concatenate a [carriage return] into a string?

[Mangetout]

<Alt><Enter>

[sandra_nz]

I think the OP is wanting to preserve carriage returns in cells when CONCATENATE-ing but is finding they dissapear. Is that correct, OP?

[The Great Sun Jester]

Interesting result there, Mr. Omnivore, and that may yet come in handy. But what I'm looking for is a character that can be concatenated between two strings that will induce a carriage return when the result is pasted into notepad/word document, etc.

The code would look something like: =concatenate(A23,?,B23,?,C23) and result in:

A23
B23
C23

[sandra_nz]

I have a feeling it's something like CHAR13?

[SCSimmons]

Originally Posted by sandra_nz

I have a feeling it's something like CHAR13?

Very close, sandra. It's CHAR(10).

[The Great Sun Jester]

=char(10) and =char(13) both work fine when I paste the result into Word. Neither work when I paste into Notepad or into the program I need to paste it into. Figures.

What I'm pasting into is a (I think) VB-based questionnaire which then loads the text into a (I think) COBOL-based display program.

[Mbossa]

CHAR(13) is a carriage return and CHAR(10) is a line feed. The correct one to use depends on your operating system. In Windows, you use a carriage return AND a line feed together (Linux uses CR by itself and MacOS uses LF by itself).

So try something like

=concatenate(A23,CHAR(13),CHAR(10),B23,CHAR(13),CHAR(10),C23)

It's likely that Word automatically detects and converts newlines from other operating systems, but Notepad and your program obviously don't.

[The Great Sun Jester]

***Plants a big sloppy kiss on Mbossa's teeth***

That did the trick!

[Mangetout]

Originally Posted by Inigo Montoya

Interesting result there, Mr. Omnivore, and that may yet come in handy. But what I'm looking for is a character that can be concatenated between two strings that will induce a carriage return when the result is pasted into notepad/word document, etc.

Ah... I didn't realise that when you said concatenate, you really meant it.

What I'm pasting into is a (I think) VB-based questionnaire which then loads the text into a (I think) COBOL-based display program.

Oh, you have one of those jobs too, I see.

[The Great Sun Jester]

Originally Posted by Mangetout

Ah... I didn't realise that when you said concatenate, you really meant it.

Oh, you have one of those jobs too, I see.

Heh...largest personal lines insurance claims operation in North America. Software cobbled together since the mid-70s and massaged elevnty-one different ways to keep pace with the legal environments for 50 states and chunks of Canada. We have software that nobody knows the origins of doing things that nobody understands for departments nobody's really sure exist anymore. There have been suggestions that we scrap the old 'warez and develop a totally modern & compatable-without-translator-programs environment that would allow underwriting, service, claims and agency to operate together seamlessly. But nobody ever listens to me.