Calculate numbers depending on words in the same cell

[BloodAngelScriptor]

I’m not sure if what I want to do is possible in a spreadsheet.

I have thousands of lines like those:

[Minute(s)=Minute(n), Hour(s)=Stunde(n),Day(s)=Tag(e), Month(s)=Monat(e)]

7 Tagen

etwa 9 Stunden

etwa 1 Monat

etwa 2 Stunden

5 Minuten

13 Tagen

10 Tagen

etwa 2 Monate

etwa 4 Stunden

9 Tagen

15 Minuten

etwa 3 Stunden

4 Minuten

8 Tagen

1 Tag

etwa 1 Monat

I want to convert those lines into minutes(numbers only), like this:

20 minutes = 20

about 2 hours = 120

4 days = 5760

about 1 month = 43200

Is there a good method for a task like this?

[JohnSUN]

@BloodAngelScriptor Among these thousands of lines, can you find lines like “3 hours 25 minutes”, “2 weeks and 3 days” or just “35” without specifying the dimension?

[BloodAngelScriptor]

@JohnSUN No, every line has only one unit.

2 minute(s)

(about) 3 hour(s)

4 day(s)

(about) 5 month(s)

[JohnSUN]

Good. Can you edit your question and add a small sample file? It’s so boring to come up with a test dataset …

[BloodAngelScriptor]

I can add a sample file but all of my data is in german, Do I’ve to translate it or do you not care?

[JohnSUN]

Better to leave it “as is” - in this case, the solution will not need to be adapted back to German. All data is not needed, only a column with samples of time

[newbie-02]

there will be plenty!, better!, more elegant solutions …

imho you can get quite far, and avoid complications from sophisticated attempts!, with the following sample:

sample based on your data

it keeps your hand and eyes on the tasks happening …

howto use: paste your data as unformatted text in cell O3 and select all available delimiters in the text import dialog, so each word ends in a single cell,

left todo:

• add columns for other time-words, ‘months’, ‘year’, …

• agree upon values for minutes in month, minutes in year or other unclear items,

• eventually add functionality for ‘number-words’, such as ‘four hours’, ‘twelve month’,

• check for completeness, zero results in col. L point to weaknesses,

regards,

[edit] with your edited question you’ll have to change the key words in the formulas [/edit]

b.

If your problem is solved please click the grey check mark () top left to the answer to turn it green.

[Lupp]

In addition to the solution already presented, I would like to post an alternative.

Anyway the questioner should define a lookup table for whatever he wants to use as an “additional unit” assigning the number of “preferred unit” it covers to it. This may be done for different languages if required, as long as there not are ambiguities.
Assuming most users nowadays use a LibO version 6.2 or higher, we can rely on the REGEX() function as a powerful means in the context to get formula-only-solutions (not requiring user interaction after changing data).

See attachment:
ask260785makeValueFromPhrase_1.ods
Better:
ask260785makeValueFromPhrase_2.ods