Is that bUG or FEATURE in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2027639%22%20slang%3D%22en-US%22%3EIs%20that%20bUG%20or%20FEATURE%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027639%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20a%20bit%20surpriced%20and%20a%20bit%20frustrated%3CBR%20%2F%3E%3CBR%20%2F%3EI%20need%20to%20%22create%20text%22%20to%20external%20source%20(actually%20automatically%20created%20tabe%20into%20Wikipedia)%3CBR%20%2F%3Edata%20I%20have%20in%20Excel%20and%20I%20tried%20to%20create%20text%20using%20Dynamic%20Arrays%20-%20and%20on%20requirement%20-%20generated%20text%20have%20to%20contain%20LF%20(char(10))%20symbol%20inside.%20And%20I%20really%20was%20surpriced%20-%20this%20works%20weird.%20Some%20reason%20-%20when%20You%20text%20contains%20Char(10)%20or%20Char(13)%20-%20excel%20(or%20who%20else%3F)%20add%20during%20copy-paste%20absolutely%20unwanted%20%22%22%20around%20line%3CBR%20%2F%3E%3CBR%20%2F%3ETHere%20is%20my%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HennSarv_0-1609684187901.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243731iBF00ED7BB4F0B956%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HennSarv_0-1609684187901.png%22%20alt%3D%22HennSarv_0-1609684187901.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20select%20column%20B%20and%20copy%20paste%20to%20notepad%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HennSarv_1-1609684228903.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243732i2B551FED201C6E47%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HennSarv_1-1609684228903.png%22%20alt%3D%22HennSarv_1-1609684228903.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOnly%20lines%20containing%20something%20like%20LF%20or%20CR%20surrounded%20with%20%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWHY%20and%20how%20to%20prevent%20this%3F%3CBR%20%2F%3E%3CBR%20%2F%3EReally%20long%20time%20Excel-user%20Henn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2027639%22%20slang%3D%22en-US%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-2027675%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20that%20bUG%20or%20FEATURE%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2027675%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F64070%22%20target%3D%22_blank%22%3E%40HennSarv%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENot%20sure%20what%20your%20desired%20result.%20Did%20you%20apply%20%22Work%20wrap%22%20formatting%20to%20the%20resulting%20cells%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2030296%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20that%20bUG%20or%20FEATURE%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2030296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3Eno%20difference%20-%20formating%20with%20wrap%20or%20without%20don't%20help%20there%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20intention%20is%20following%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HennSarv_1-1609782407321.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243922i8CE1D0609D607875%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HennSarv_1-1609782407321.png%22%20alt%3D%22HennSarv_1-1609782407321.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eon%20col-s%20A%3AF%20is%20'unsorted'%20list%20of%20people%20to%20be%20generated%20as%20alphabetically%20sorted%20list%20for%20Wikipedia%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HennSarv_2-1609782547321.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243923iDE27C37D9D1EF050%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HennSarv_2-1609782547321.png%22%20alt%3D%22HennSarv_2-1609782547321.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eusing%20following%20formulas%20(might%20be%20better%20idea)%3CBR%20%2F%3Ecol%20I%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3DUNIQUE(LEFT(INDEX(A2%23%3B%3B4)%3B1))%3CBR%20%2F%3Ecol%20J%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3D%22%3D%3D%22%26amp%3BUNIQUE(LEFT(INDEX(A2%23%3B%3B4)%3B1))%26amp%3B%22%3D%3D%22%26amp%3BCHAR(13)%26amp%3BCHAR(10)%26amp%3B%22%7B%7Bt%C3%A4hed%7D%7D%22%3CBR%20%2F%3Ecol%20L%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HennSarv_3-1609782689688.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F243924iDFE3D29BA3A3EEB7%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22HennSarv_3-1609782689688.png%22%20alt%3D%22HennSarv_3-1609782689688.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eresult%20must%20contain%20ORDERED%20list%20of%20names%20inserted%20with%3CBR%20%2F%3E%3D%3DX%3D%3D%3CBR%20%2F%3E%7B%7Bt%C3%A4hed%7D%7D%3CBR%20%2F%3Efor%20each%20letter%3CBR%20%2F%3E%3D%3DX%3D%3D%20produces%20heading%20style%20row%20in%20wikipedia%3CBR%20%2F%3E%7B%7Bletters%7D%7D%20produces%20linkable%20letter-index%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%203%20ways%20to%20producse%20this%20result%3CBR%20%2F%3E*%20old-style%20excel%20functions%20(yes%20sorting%20was%20available%20with%20old%20excel%20functions)%3CBR%20%2F%3E*%20PowerQuery%20cenerating%20desired%20result%3CBR%20%2F%3E*%20Dynamic%20Array%20-%20as%20above%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20concern%20is%20-%20WHY%20cells%20containing%20LF%20or%20CR%20during%20copy-paste%20generation%20unwanted%20%22%22%20around%3CBR%20%2F%3Eresult%20is%20that%20I%20have%20during%20copypaste%20land%20in%20%22notepad%22%2C%20Ctrl-H%20remove%20%22-s%20and%20after%20that%20copypaste%20to%20wikipedia%20source%3CBR%20%2F%3E%3CBR%20%2F%3EHenn%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm a bit surpriced and a bit frustrated

I need to "create text" to external source (actually automatically created tabe into Wikipedia)
data I have in Excel and I tried to create text using Dynamic Arrays - and on requirement - generated text have to contain LF (char(10)) symbol inside. And I really was surpriced - this works weird. Some reason - when You text contains Char(10) or Char(13) - excel (or who else?) add during copy-paste absolutely unwanted "" around line

THere is my example

 

HennSarv_0-1609684187901.png

I select column B and copy paste to notepad:

HennSarv_1-1609684228903.png

Only lines containing something like LF or CR surrounded with "

 

WHY and how to prevent this?

Really long time Excel-user Henn

5 Replies

@HennSarv 

Not sure what your desired result. Did you apply "Work wrap" formatting to the resulting cells?

@Sergei Baklan 

no difference - formating with wrap or without don't help there

My intention is following

HennSarv_1-1609782407321.png

on col-s A:F is 'unsorted' list of people to be generated as alphabetically sorted list for Wikipedia

HennSarv_2-1609782547321.png

using following formulas (might be better idea)
col I     =UNIQUE(LEFT(INDEX(A2#;;4);1))
col J     ="=="&UNIQUE(LEFT(INDEX(A2#;;4);1))&"=="&CHAR(13)&CHAR(10)&"{{tähed}}"
col L

HennSarv_3-1609782689688.png

result must contain ORDERED list of names inserted with
==X==
{{tähed}}
for each letter
==X== produces heading style row in wikipedia
{{letters}} produces linkable letter-index 

I have 3 ways to producse this result
* old-style excel functions (yes sorting was available with old excel functions)
* PowerQuery cenerating desired result
* Dynamic Array - as above

My concern is - WHY cells containing LF or CR during copy-paste generation unwanted "" around
result is that I have during copypaste land in "notepad", Ctrl-H remove "-s and after that copypaste to wikipedia source

Henn





 

  

@Sergei Baklan 

 

Just tested - looks like some kind of hardcoded feature in excel

ANY cell containing LF of CR (normally ALT-ENTER) will produce "" around during copy-paste into other application (form)

VERY strange

 

@HennSarv 

Yes, I forgot about this issue. Here is quite old discussion formatting - How to copy multi-line text from Excel without quotes? - Super User , there are few more, but I didn't see proper solution.

@Sergei Baklan 

I know how to solve with MY case (I replace 2 array union wit 3 array union) but conceptually this is very bäd issue