@Villeroy Not, if he insist to have both languages in the same cell/field…
1 Like
- Select the column.
- Do: Menu: Data - Text to Columns… (split by space).
Yes. That’s what @Lupp did in your topic that you linked to here somewhere.
This can be changed by editing the regular expression in the SplitText() function.
sPattern = "[^\w||-]+" << Regex pattern is set here
(OR: "[\W--[-]]", "[\W---]", "[\W&&[^-]]")
How would you like to handle punctuation marks?
They are now being completely removed.
Edit:
The attached file is updated. Look for ‘vocabulary (3).ods’.
The vocabulary does not allow the inclusion of duplicates (insensitive to case) now.
If you add a word that is already in the vocabulary, you will be prompted to edit the meaning(s) of the word yourself.
This is absurd. The data in the field must be atomic, i.e. indivisible. The field in question is, for all intents and purposes, a calculated field. Not every “want” has to be satisfied. And if satisfied, then exactly insofar as it will be paid for the time spent on it. I see nothing wrong with directing the user in the right direction.
Splitting by space is a crutch regarding the topic.
Reasonable dictionary will never just consist of “word-equations”. The given example was English/French. Such a “dictionary” must contain (e.g)
cat::le chat
cat:: la chatte
flower:: la fleur
flower la farine (Excuse the joke.)
In general English rarely regards a grammatic gender, but sometimes does.
We can offer lots of workarounds (and I already considered some), but will never overcome the fundamental inaptness of the concept.
You are right, but if somebody don’t accept basic principles it is useless to direct to databases… We can try to direct people, but if somebody thinks he can re-invent everything - good luck…
Just for completion:
Users capable of reading German may
study the old thread Calc: nach erstem kursiven Zeichen suchen. There the data were created by “OCR” (What’s optical about it? The recognition is attempted after the scan.) from a printed list which used standard font for one language and italic for the other. Human inventiveness seems to be bottomless concerning inapt presentation - and traditional prints are great at it.
Well, there were reasons, but men (f/m) won’t change things just because of reasons.
Nowadays printed media seem particularly proud of silly formatting.
Only “bi”? Queerlingue is up to date.
@Wanderer, e.g. see link.
The pseudo-problem of on-the-fly data filtering is solved there. I, alas, do not share such approaches, because there should be little code, and it is better if there is no code at all. Not everything that is possible is good.
The topic is confusing. I will now start a new topic with the same title (2). However, you are offered a solution, and the data conversion problem is a separate issue.
@Frederico2, I don’t understand everything you write. I need a new example of real data.
Go to new topic.
Such an approach is beyond my understanding.
If I got it right, I could only think of a reorganized sheet containing the words witthout any flection and atomic per language. Any pair of adapted/declined/including words or phrases might then be created by formula in a dedicated column. To find any of them based on a partial input would then not be a case for AutoInput, but one for finding matches and of listing/linking them in one ort another way.
It’s not very complicated to show a list of rows (their numnbers) containing a matching cell as soon as the input is finished. If it must be done during input I can’t imagine a solution without a lot of programming.
@Frederico2, questions remain. We think in terms of cells and formulas, because this is a spreadsheet. But you use it differently somehow. And it’s not quite clear to me how.
This is completely incomprehensible. Send us an piece of your table. And forgive me for the harshness of what I said above.
And why can’t it go side by side, but in different cells?
@Frederico2, you have not fully covered the task. Try to summarize the result and report something new. With specific examples. It is difficult for us to model your actions and your actual information. Go to the new topic (2) that I created for you. It’s impossible to figure out here.
Yes, but instead of hitting the space bar, the rules say you should have inserted a word search formula in the cell on the right. The result would be the same. And if you insert many words at once, you can insert many formulas in the columns on the right with one double-click (I’ll explain how, if you’re interested) and see the translation, rather than clicking on each cell and inserting a space…
It gives the impression that you underestimate the power of spreadsheets and think you’re doing the right thing. We don’t think you are. 
Edit:
The judgment is unreasonable.
The point is that your problem needs to be considered anew, discarding everything original.
Could you please send me a snippet of the spreadsheet. Ten rows of data.
I’ll think about it. One advantage of your approach I saw: you don’t have to enter the first word in its entirety, because AutoInput offers variants as you type for a ready pair of words. It makes a difference how the words are entered: one word at a time or several words in several rows (copy&paste).
Where does the data come from? I need to understand how you work. Describe your workflow. Let’s push the boundaries and not solve a particular problem, but try to encompass the whole. With private problems formulated as you understand them, you are leading us away from the solution.
Got it. You should accumulate words with translation in your dictionary, where word forms are stored as you wish, and you need a macro to split the sentence by words and find the meaning in the dictionary for each word. For example: you paste the sentence in the first cell of the current row, call the macro that writes all words of the sentence in the column A, starting from the next row, finds the translation in the dictionary and enters it into the cell to the right of the word that is looking for. If the word is not translated, you do the translation yourself and call the macro to add the new word to the dictionary. Then you go to the next empty row and paste the new sentence. Everything repeats. This is the working sheet. You can clear the range and start over.
Macros:
Sub TranslateTextByWords()
↳ Function SplitText()
↳ Sub TranslateWords()
Sub AddWordToVocabulary()
↳ Sub UpdateVocabulary()
↳↳ Sub SortVocabulary(oRange As Object)
So, two macros are needed. The spreadsheet document will be consist of 2 sheets (Translate, Vocabulary). Is it right?
You may not know what a macro is. It does not matter. Is the process I described appropriate for the task at hand? If so, there will be those who want to implement this algorithm. Then you won’t have to click anymore. Unless with a few exceptions.
A macro is a program that solves a specific task.
AutoInput also looks for something in your column, but here it will look in your vocabulary, which you will automatically maintain.
If I have time. There are many people here who work more productively than I do. @JohnSUN, for example (helps a lot of people). The main thing is that it was possible to explain the whole process.