String extraction with a number of conditions?

[Rnkhch]

Hello,

I have a column of data with patient visit day information which looks like below:

Day x(text) - y min (text)
Day x(text) - y-z min (text)
Day x(text) - y hr (text)
Day x(text) - y-z hr (text)
Day x(text) - pre (text)
Day x(text) - baseline (text)

where (text) implies that some text may or may not be at those positions.

My goal is to come up with a formula to get the "min" and "hr" numbers, i.e. the numbers immediately preceding "min" and "hr". Also if "pre" or "baseline" are mentioned, I want those words as the output. And if "min", "hr", "pre" or "baseline" are not mentioned, I want an "N/A" output. One issue is that the data is entered sloppily and not all the spaces are necessarily there Also it would be really nice to get rid of the spaces in between the numbers and the hyphen for hour and min info. Here is an XL2BB sample of inputs and outputs that I need:

Book1
	A	B	C
1	Input	Desired output
2	Day 17	N/A
3	Day 1 - 0-60 min Post	0-60 Min
4	Day 1 - pre	Pre
5	Day 1 - 7 Hr Post	1-7 Hr
6	Day 1 - pre(visit1)	Pre
7	DAY 1(VISIT 1) - 2-4 hr Post	2-4 Hr
8	DAY 1(VISIT 2) - 0-1 Hr Post(only visit 2)	0-1 Hr
9	Baseline -	Baseline
10	DAY 33(VISIT 9) -	N/A
11	Day12(baseline)	Baseline
12	Day 11 - 7Hr Post	7 Hr
13	DAY 23 - 6 - 8 hr Post	6-8 Hr
14
Sheet1

Thanks for any input!

 

[Anthony47]

Thank you for the feedback
If that resolve the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution

 

[Automatrix]

@Anthony47
It worked very well with my current dataset; thanks much! I will let you know in case I need some adjustments if I can't figure out on my own (depending on what I will encounter in other data sets).

@Automatrix
Thank you! I haven't used Power Query yet, but I heard about it a few weeks ago, and I'm excited to try and learn it. If you know a website with a good intro/pointers, please share the link.

I've just used the old method of trying it out and googling whenever I had a specific problem. In this case I would go to the Add Column tab and add conditional columns (eg. if the word "baseline" is there, write "baseline", else nothing) or extractions (e.g. everything before "min"). This will probably generate a lot of helper columns, which can be merged into one column at the end.