Spreadsheet copy limited range

English
, ,
#1

I’m trying to solve a problem I have. I’m learning Russian and have over 50 pages in a Writer .odt document where I list Cyrillic Russian words with their English meanings. I should have thought this out from the start but I now want to be able to use a LO text to speech extension to pronounce the Russian words. I should have saved them in a table or a spreadsheet where I kept all the Russian words in a column and the English translations in a second column. Instead, I have them alternating Russian word, English translation one line (paragraph) after another for over 50 pages. I’ve tried setting the language so I can have the Russian pronunciations ignore the English translation lines, but I want the translations because I have not yet memorized all the meanings.

I tried to get Gemini AI to help me with that and it took me on the most incredibly long, circuitous, mind boggling variations and attempts that ALL failed. The one that seemed to me like it might possible be workable was copying all the pages into a Calc spreadsheet into the A1 cell. Then in the B1 cell I used this formula. =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)2-1,0) then in the C1 cell I used this one =INDEX($A:$A, 2ROW())

I tried to copy them to the end of the document (over 50 pages) but it died out on about row 1457 then began filling in the rest with zeros. I tried forcing recalculations using Ctrl+Shift+F9, but that did not work. Can anyone help me with this? I tried writing a macro, running Ctrl-H Find and Replace, and a ton other things. I’m stuck.

I don’t want to manually find and replace anything on a document over 50 pages long (and I have multiple such documents). I want to keep the ability to listen to correct pronunciations but need also to keep English translation meanings. I don’t wan to have to sign up for a paid service.

#2

Ola @silvanet , é possível postar um exemplo do arquivo?

Hello @silvanet, is it possible to post an example of the file?

#3

Well, this sucks. Asa newbie here I’m only allowed to embed one file. I don’t know if the image file also has to be uploaded to render in markdown. Let me know what best would help you now. I could take tiny file samples and upload them instead. The .odt and .ods, but am I allowed to upload more than one file as a newbie?

#4

Just save one page worth as a sample and then upload using the Upload icon, 7th from left with tooltip Upload. Or drag the sample file from your file manager on to an open comment, or reopen the question if you still can.

Maybe try pasting into a new document rather the end of an exiting one.

#5

That doesn’t help. I don’t know if he wanted a sample of the original .odt file or of the .ods where I tried to recreate two separate columns. If I can only upload one, I need to get a response back from him exactly which, because of my newbie limitations.

#6

We need a sample of your original . odt to analyze the precise separation of the languages.
.
It may be possible to do the separation by find/replace or one needs to write a macro…
It can be an easy task, if you have a clear separator like a tab between languages.
If not the next look can go to your term “Russian words”. If it is really one word there are regular expressions usable.
My last resort would be to separate kyrillic letters from western spelling in a script.

#7

I guess it is one file per posting so you could try to upload the “target” to a second comment. Just try. There may also be a time limit. The feature is there to limit spam and advertisments.

#8

Ok, here’s a small section of the original .odt. I tried to get Gemini to help me but every single attempt it took me through to do find/replace, AND writing a macro failed.
Russian_English.odt (13.7 KB)

#9

Try of the .ods that started filling zeros
Russian_English.ods (22.9 KB)

#10

OK, great, I was able to upload both. If you have any questions about what I wanted to do, let me know. Thanks.

#11

@silvanet, is this what you want?

Russian_English_GS.ods (31,7,KB)

#12

No, that is what I had (cut off to about one of over 50 pages so you could see where the copy stopped processing the formulas and began rendering zeros for over 40 of those pages. I see what you return has no zeros, but that is not sufficient to explain what was happening and why I could not force recalculation. I just copied column A and pasted it a couple of times, extending that column. Then I selected the last filled row of Columns B and C and pasted it down to the end of the extended data in Column A. It did fill with data and no zeros, but I need to know what caused it to begin filling zeros (as in the short example I first gave you.

I can try to reinsert all my 50 pages of data and see if it now pastes the calculated formulas and not zeros for the entire 50 pages.

Also, that is only the first step of what I wanted. My intent is to use the Read selection extension speech to text to listen to the pronunciation of the Russian words while keeping visible the column that gives me the English translation meanings. I don’t want to hear the English words pronounced, only the Russian words in the selected column. That is in essence what I am trying to reach.

Well, that does it. I lost my 50 pages of Russian words altogether. Forget it now, I’ll have to start recompiling my list all over again. I don’t now if it is in a backup, but I’m distraught.

#13

The fike can easily converted in a 2-column csv for import to Calc, but I used Notepad++ for this

  • Save te file as txt (Charset utf8)
  • Replace the bullets with surrounding spaces by a marker @
  • Replace all line-breaks wth a separator |
  • Now re-insert line-breaks by replacing |@ with \r\n
  • Save the text-file as NewName.csv
  • Import the csv in Calc with | as separator

??

#14

Sorry, I did have a backup and was able to restore my previous work. I must have bungled while I was copying, deleting, and pasting to make short example files for you.

So, OK, I was able to paste my original list of alternating Russian words with their English meanings in the next paragraph for an entire 50 pages.

My first intention was to use the LO text to speech extension to read me the Russian words. I went through everything I could figure to properly configure that so it would ignore the English lines when nit read the Russian words. That didn’t work.

So then I thought, maybe if I had designed my file originally to separate the Russian words from the English definitions by separate table columns, then I could use the TTS extension to read the Russian words only by selecting the Russian words column.

Gemini AI tried to help me use Find/Replace and writing a macro but both of those kept failing in the Find stage.

Next, I thought OK, let me try to transfer all of this over to a spreadsheet and try from there to figure out a way to have only the Russian words column be read.

That’s where I was when Gemini began to recycle its suggestions back to the very same it had tried from the beginning. I gave up and thought I’d come over here for some help.

I took your “somehow fixed” spreadsheet and deleted all its contents from the A column. Then I copied my text from my complete .odt file into your .ods cell A1, so now I filled your .ods (the Russian_Englis_GS.ods file, then did a Data, Text to Columns. It filled all my original data into column A.

Then I put my formulas in B1 and B2. For your immediate reference, they were B1: =OFFSET($A$1,(COLUMN()-1)+(ROW()-1)2-1,0) and in C1: =INDEX($A:$A, 2ROW())

Ok, now I selected all of column B using those two columns and copied their formulas down to the end of the Column A content. They all processed the formulas.

I think I figured out why the formulas stopped returning any content and just returned zeros. They had calculated and processed all the existing data in the original column A. Boy did I miss that! I could confirm that by pasting the contents of the formula created columns as a paste special of contents only.

My problem really did not need solving. I was imagining it. All the data rendered properly and now I do have the Russian words in one column and the English definitions in a second adjacent column.

What I now need to do is to make a TTS reader extension read my Russian words in Column A only, leaving the English definitions column alone.

Can you help me configure the Read Text extension to read the Russian words?

#15

That is exactly what Gemini had me do and it fails. I don’t need to keep dancing around that merry-go-round. You did help me get the columns to work. I copied them to content only columns and can select the Russian column and use Tools > Add-Ons > Read Selection to have the TTS read the whole list of Russian words. Thanks for your help. I’d say I was successful thanks to you.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.