Convert text to number

[JAGBI]

Hi All,

I have the data in the table below and trying to generate the column Puff Per Day based on the text in the MedicationDirection column.

Any ideas how this can be achieved please?

MedicationDirections	Puff Per Day	Note
Inhale TWO puffs by mouth daily	2
Inhale TWO puffs by mouth daily as directed by the doctor	2
Shake well and inhale 1 puff by mouth TWICE daily l14	2
Shake well and inhale 1 puff by mouth Use as directed by the doctor	0	No duration so value is 0
Shake well and inhale 1 puff by mouth in the morning	1
Shake well and inhale 2 puffs by mouth THREE times daily	6
Shake well and inhale 2 puffs by mouth every EIGHT hours when required	6	24 hrs / 8 hrs * 2
Shake well and inhale 2 puffs by mouth every FOUR hours when required	8
Shake well and inhale 2 puffs by mouth every SIX hours when required	12
Shake well and inhale 2-4 puffs by mouth at once when necessary	0	No duration so value is 0
Shake well and inhale 2-4 puffs by mouth every SIX hours when required Use as directed by the doctor	12	24 hrs/6 hrs * 3 (avg puffs)
Shake well and inhale EIGHT puffs by mouth every FOUR hours as directed by your physician	48	24 hrs /4 hrs * 8 puffs
Shake well and inhale ONE to TWO puffs by mouth TWICE a day
Shake well and inhale ONE to TWO puffs by mouth TWICE a day *Rinse mouth with water after each use*	3	1.5 puff x 2 times a day
Shake well and inhale TWO puffs by mouth FOUR times a day as directed when necessary	8
Shake well and inhale TWO puffs by mouth TWICE a day	4

Thanks

Jag

[TarunDeep]

Hi @JAGBI ,

I was able to build a solution using Regex and formula tools.

It gives Quantity for all the records.

Please check if that serves the purpose. Mark it as a solution if it solves the problem.

[JAGBI]

Hi Tarun,

This is great help. Would you be able to help with the below in RED as well please

MedicationDirections	Puff Per Day	Note
Inhale TWO puffs by mouth daily	2
Inhale TWO puffs by mouth daily as directed by the doctor	2
Shake well and inhale 1 puff by mouth TWICE daily l14	2
Shake well and inhale 1 puff by mouth Use as directed by the doctor	0	No duration so value is 0
Shake well and inhale 1 puff by mouth in the morning	1
Shake well and inhale 2 puffs by mouth THREE times daily	6
Shake well and inhale 2 puffs by mouth every EIGHT hours when required	6	24 hrs / 8 hrs * 2
Shake well and inhale 2 puffs by mouth every FOUR hours when required	8
Shake well and inhale 2 puffs by mouth every SIX hours when required	12
Shake well and inhale 2-4 puffs by mouth at once when necessary	0	No duration so value is 0
Shake well and inhale 2-4 puffs by mouth every SIX hours when required Use as directed by the doctor	12	24 hrs/6 hrs * 3 (avg puffs)
Shake well and inhale EIGHT puffs by mouth every FOUR hours as directed by your physician	48	24 hrs /4 hrs * 8 puffs
Shake well and inhale ONE to TWO puffs by mouth TWICE a day	0
Shake well and inhale ONE to TWO puffs by mouth TWICE a day *Rinse mouth with water after each use*	3	1.5 puff x 2 times a day
Shake well and inhale TWO puffs by mouth FOUR times a day as directed when necessary	8
Shake well and inhale TWO puffs by mouth TWICE a day	4
Inhale ONE dose by mouth daily #SC4711	1
shake well and inhale 2 puffs by mouth morning and night	4
inhale 1 dose puff in the morning and take 2 puffs at night	3
inhale 1 dose by mouth daily - *regular dosage essential* #sc4711	1

Thanks

Jag

[TarunDeep]

PFA the new solution, simple to split the string using RegEx. and use filters for directing the results.