Is that bUG or FEATURE in excel

Brass 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