EXCEL Question: Automatic new line / doubling new line commands

%3CLINGO-SUB%20id%3D%22lingo-sub-2375123%22%20slang%3D%22de-DE%22%3EEXCEL%20Question%3A%20Automatic%20new%20line%20%2F%20doubling%20new%20line%20commands%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375123%22%20slang%3D%22de-DE%22%3E%3CP%3EHey%20guys%3C%2FP%3E%3CP%3EI%20have%20this%20in%20one%20cell%3A%3C%2FP%3E%3CP%3E%E2%80%A2%20With%20the%20S3000%20LED%20working%20headlight%2C%20HELLA%20brings%20the%20strongest%20VALUEFIT%20working%20headlight%20to%20the%20market%20to%20date%20%3CBR%20%2F%3E%20%E2%80%A2%20800%20mm%20cable%2C%20GERMAN%20plug%20in%20the%20scope%20of%20deliveryExcel%20new%20line%20command%3C%2FP%3E%3CP%3E%E2%80%A2%20With%20its%203%2C000%20lumens%2C%20it%20is%20ideal%20for%20applications%20with%20a%20lot%20of%20light%20requirements%20and%20is%20even%20comparable%20to%20xenon%20light%20performance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20automatically%20want%20it%20to%20look%20like%20this%20(with%20one%20empty%20line%20between)%3A%3C%2FP%3E%3CP%3E%E2%80%A2%20With%20the%20S3000%20LED%20working%20headlight%2C%20HELLA%20is%20launching%20the%20strongest%20VALUEFIT%20working%20headlight%20to%20date%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%E2%80%A2%20800%20mm%20cable%2C%20GERMAN%20plug%20included%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20%E2%80%A2%20With%20its%203%2C000%20lumens%2C%20it%20is%20ideal%20for%20applications%20with%20a%20lot%20of%20light%20requirements%20and%20is%20even%20comparable%20to%20Xenon%20light%20performance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20possibility%20to%20do%20this%3F%20In%20my%20opinion%20there's%203%20options%3A%3C%2FP%3E%3CP%3E1-%20with%20%22format%20cell%22%20by%20adding%20a%20new%20line%20command%20at%20the%20custom%20settings%3C%2FP%3E%3CP%3E2%20-%20by%20telling%20excel%20somehow%20to%20double%20all%20new%20line%20commands%20(as%20of%20course%20there%20is%20already%20one%20ALT%2BRETURN%20between%20the%20lines%2C%20so%20if%20that's%20doubled%20there'll%20be%20an%20empty%20line%20between)%3C%2FP%3E%3CP%3E3-%20by%20a%20command%20in%20another%20row%20that%20uses%20the%20original%20cell%20and%20endoubles%20the%20new%20lines...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20my%20non-technical%20english%2C%20I%20hope%20you%20understand%20what%20I%20mean%20%3A).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThx%20a%20lot!%20Sebastian%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2375123%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375156%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20Question%3A%20Automatic%20new%20line%20%2F%20doubling%20new%20line%20commands%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375156%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059815%22%20target%3D%22_blank%22%3E%40sebsonntag%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20such%20a%20value%20in%20cell%20A2.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20B2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUBSTITUTE(A1%2CCHAR(10)%2CCHAR(10)%26amp%3BCHAR(10))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20German%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DWECHSELN(A1%3BZEICHEN(10)%3BZEICHEN(10)%26amp%3BZEICHEN(10))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20Wrap%20Text%20is%20turned%20on%20for%20B2.%3C%2FP%3E%0A%3CP%3EYou%20can%20fill%20this%20down%20if%20required.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hey guys,

I have this in one cell:

• Mit dem S3000 LED Arbeitsscheinwerfer bringt HELLA den bisher stärksten VALUEFIT Arbeitsscheinwerfer auf den Markt
• 800 mm Leitung, DEUTSCH-Stecker im LieferumfangExcel new line command 

• Mit seinen 3.000 Lumen eignet er sich optimal für Anwendungen mit viel Lichtbedarf und ist sogar mit Xenon Lichtperformance vergleichbar

 

And I automatically want it to look like this (with one empty line between):

• Mit dem S3000 LED Arbeitsscheinwerfer bringt HELLA den bisher stärksten VALUEFIT Arbeitsscheinwerfer auf den Markt

• 800 mm Leitung, DEUTSCH-Stecker im Lieferumfang

• Mit seinen 3.000 Lumen eignet er sich optimal für Anwendungen mit viel Lichtbedarf und ist sogar mit Xenon Lichtperformance vergleichbar

 

Is there a possibility to do this? In my opinion there's 3 options:

1- with "format cell" by adding a new line command at the custom settings 

2 - by telling excel somehow to double all new line commands (as of course there is already one  ALT+RETURN between the lines, so if that's doubled there'll be an empty line between)

3- by a command in another row that uses the original cell and endoubles the new lines...

 

Sorry for my non-technical english, I hope you understand what I mean :).

 

Thx a lot! Sebastian

1 Reply

@sebsonntag 

Let's say you have such a value in cell A2.

Enter the following formula in B2:

 

=SUBSTITUTE(A1,CHAR(10),CHAR(10)&CHAR(10))

 

In German:

 

=WECHSELN(A1;ZEICHEN(10);ZEICHEN(10)&ZEICHEN(10))

 

Make sure that Wrap Text is turned on for B2.

You can fill this down if required.