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:
Thanks for any input!
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!