# Calc: Help formatting a incredibly long column

I want to create a table spreadsheet of the Unicode characters. The file I have lists them in seperate lines. Pasting into Excel it creates one column several thousand rows deep. I have not been able to get this data into a more square format say 50 rows deep and however many columns wide. Merging the columns to then be seperated again crashes the program.

edit retag close merge delete

Sort by » oldest newest most voted

"The Unicode character" may be misleading. UTF-8 should offer codeplaces (in "hex") from 000000 to 10FFFF (?) being 2^20+1, and thus in "dec" 1048577. This also is nearly exactly the number of rows a Calc spreadsheet has. (One row missing.(?)) As far as I know the (current) ultimtae extension of the Unicode sytem allows for full 4 Byte coding resulting in 4294967296 (=2^32) codeplaces being the number of cells of 4 complete spreadsheets.
=DEC2HEX(ROW(A1)-1;6) entered into A1 and filled down to the last row would create the 6-digit-hex representations of all the UTF-8 codeplaces. =UNICHAR(HEX2DEC(A1)) in B1 and also filled down would (theoretically) supply the characters and show the glyphs.

Filling such formulae down to the last row would require a lot of time and likely fail anyway. If coming to an end at all it would create a multimillion-byte-file.

Splitting the table to many columns is, of course, feasible with similar formulae. Using 16 columns e.g. for the codeplaces and as many for the characters would reduce the number of rows to 65536. The problems concerning time and filesize would be (roughly) unchanged.

See this demo.

more

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Thanks Lupp, I can use this to get the rest of the stuff I pulled from the wiki organized. Super cool of you to do this for me. Thank you.

more

What you posted here is not an answer in this sense, but a comment on my answer.
In future use the tool 'add a comment' for this purpose.
If you judge my answer to be correct (or very helpful) you should accept it by clicking the checkmark left of its first lines.
(Wiki posts make not much sense in such cases.)

( 2018-05-14 13:17:21 +0100 )edit