Ask Your Question

Calculate numbers depending on words in the same cell

asked 2020-08-18 08:42:23 +0200

BloodAngelScriptor gravatar image

updated 2020-08-18 12:54:29 +0200

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?

edit retag flag offensive close merge delete


@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?

JohnSUN gravatar imageJohnSUN ( 2020-08-18 10:52:06 +0200 )edit

@JohnSUN No, every line has only one unit.

2 minute(s)

(about) 3 hour(s)

4 day(s)

(about) 5 month(s)

BloodAngelScriptor gravatar imageBloodAngelScriptor ( 2020-08-18 11:19:44 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2020-08-18 11:24:25 +0200 )edit

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?

BloodAngelScriptor gravatar imageBloodAngelScriptor ( 2020-08-18 12:35:52 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2020-08-18 12:42:35 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2020-08-18 12:51:34 +0200

newbie-02 gravatar image

updated 2020-08-18 13:09:15 +0200

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,


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


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

edit flag offensive delete link more

answered 2020-08-18 14:25:58 +0200

Lupp gravatar image

updated 2020-08-18 15:48:13 +0200

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:

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-18 08:42:23 +0200

Seen: 86 times

Last updated: Aug 18 '20