SOLVED
Home

Excel 365 Insider (2004 Click to run) CONCAT function with CHAR(10) to insert line feed not working

%3CLINGO-SUB%20id%3D%22lingo-sub-1283356%22%20slang%3D%22en-US%22%3EExcel%20365%20Insider%20(2004%20Click%20to%20run)%20CONCAT%20function%20with%20CHAR(10)%20to%20insert%20line%20feed%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283356%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20attempting%20to%20insert%20line%20feeds%20into%20text%20using%20a%20function.%20From%20everything%20I%20can%20find%20the%20CHAR(10)%20used%20within%20CONCAT%2C%20TEXTJOIN%20or%20plain%20old%20%22%26amp%3B%22%20should%20give%20me%20the%20results%20I'm%20looking%20for%20but...%20NOT.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20test%20code%20is%20%3DCONCAT(%22A%22%2CCHAR(10)%2C%22B%22%2CCHAR(10)%2C%22C%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20expected%20result%20is%3A%3C%2FP%3E%3CP%3EA%3C%2FP%3E%3CP%3EB%3C%2FP%3E%3CP%3EC%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20actual%20result%20is%20ABC%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETEXTJOIN%20and%20%22%26amp%3B%22%20gives%20me%20the%20same%20result%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20copied%20and%20pasted%20sample%20formulas%20but%20my%20results%20are%20totally%20different%20from%20the%20examples.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPC%2C%20Windows%2010%20Pro%20Excel%20365%20Insider%20(Not%20online)%20All%20update%20applied%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20anyone%2C%20please%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20TheOldPuterMan%20(AKA%20John)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1283356%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1283404%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20Insider%20(2004%20Click%20to%20run)%20CONCAT%20function%20with%20CHAR(10)%20to%20insert%20line%20feed%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283404%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F317593%22%20target%3D%22_blank%22%3E%40TheOldPuterMan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20formula%20is%20correct%2C%20you%20need%20to%20wrap%20text%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Snag_163988df.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182284i04E93D3DF4660EE1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Snag_163988df.png%22%20alt%3D%22Snag_163988df.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%3C%2FSPAN%3E.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1283424%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20Insider%20(2004%20Click%20to%20run)%20CONCAT%20function%20with%20CHAR(10)%20to%20insert%20line%20feed%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283424%22%20slang%3D%22en-US%22%3EThank%20you!%20That%20wasn't%20mentioned%20anywhere%20and%20I%20was%20too%20naive%20to%20consider%20it.%3CBR%20%2F%3ETheOldPuterMan%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1314691%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20365%20Insider%20(2004%20Click%20to%20run)%20CONCAT%20function%20with%20CHAR(10)%20to%20insert%20line%20feed%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1314691%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUhhhh%20Doh!%20in%20the%20vernacular%20of%20Homer%20Simpson%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Bad%20on%20that%2C%20I%20presumed%20that%20the%20line%20feed%20would%20be%20automatic.%20Kinda%20shows%20my%20age%20huh.%20Used%20to%20work%20on%20the%20old%20Dot%20Matrix%20keyboards%20I%20used%20long%20ago%20and%20on%20ASCII%20based%20terminals.%20I've%20been%20around%20a%20long%20time%20and%20every%20now%20and%20again%20it%20shows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20OldPuterMan%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BAKA%20John%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

I'm attempting to insert line feeds into text using a function. From everything I can find the CHAR(10) used within CONCAT, TEXTJOIN or plain old "&" should give me the results I'm looking for but... NOT.

 

My test code is =CONCAT("A",CHAR(10),"B",CHAR(10),"C")

 

The expected result is:

A

B

C

 

The actual result is ABC

 

TEXTJOIN and "&" gives me the same result

 

I've copied and pasted sample formulas but my results are totally different from the examples.

 

PC, Windows 10 Pro Excel 365 Insider (Not online) All update applied

 

Help anyone, please

 

Thanks, TheOldPuterMan (AKA John)

3 Replies
Highlighted
Solution

hi @TheOldPuterMan 

 

You formula is correct, you need to wrap text 

Snag_163988df.png

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

 

Highlighted
Thank you! That wasn't mentioned anywhere and I was too naive to consider it.
TheOldPuterMan
Highlighted

@Faraz Shaikh 

Uhhhh Doh! in the vernacular of Homer Simpson

 

My Bad on that, I presumed that the line feed would be automatic. Kinda shows my age huh. Used to work on the old Dot Matrix keyboards I used long ago and on ASCII based terminals. I've been around a long time and every now and again it shows.

 

The OldPuterMan     AKA John