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]

Try in B2 the following formula:

=LET(sStr,A2,aStr,UPPER(TEXTAFTER(sStr,"-")),kW,TEXTSPLIT( UPPER("min,hr,pre,baseline"),,","),LFor,TEXTJOIN("",TRUE, IFERROR(IF(FIND(kW,aStr)>0,kW,""),"")),pOut,IFERROR(IF(FIND("BASELINE",UPPER(sStr))>0,"Baseline",""),TRIM(TEXTBEFORE(aStr,LFor)&" "&PROPER(LFor))),IF(LEN(pOut)>2,pOut,NA()))

BR1 Sales_Ledger RECON(9.2.3) ott-2022
	A	B	C	D
1	Input	Desired output		Formula Out
2	Day 17	N/A		#N/D
3	Day 1 - 0-60 min Post	0-60 Min		0-60 Min
4	Day 1 - pre	Pre		Pre
5	Day 1 - 7 Hr Post	1-7 Hr		7 Hr
6	Day 1 - pre(visit1)	Pre		Pre
7	DAY 1(VISIT 1) - 2-4 hr Post	2-4 Hr		2-4 Hr
8	DAY 1(VISIT 2) - 0-1 Hr Post(only visit 2)	0-1 Hr		0-1 Hr
9	Baseline -	Baseline		Baseline
10	DAY 33(VISIT 9) -	N/A		#N/D
11	Day12(baseline)	Baseline		Baseline
12	Day 11 - 7Hr Post	7 Hr		7 Hr
13	DAY 23 - 6 - 8 hr Post	6-8 Hr		6 - 8 Hr
Foglio12

Cell Formulas
Range	Formula
D2:D13	D2	=LET(sStr,A2,aStr,UPPER(TEXTAFTER(sStr,"-")),kW,TEXTSPLIT( UPPER("min,hr,pre,baseline"),,","),LFor,TEXTJOIN("",TRUE, IFERROR(IF(FIND(kW,aStr)>0,kW,""),"")),pOut,IFERROR(IF(FIND("BASELINE",UPPER(sStr))>0,"Baseline",""),TRIM(TEXTBEFORE(aStr,LFor)&" "&PROPER(LFor))),IF(LEN(pOut)>2,pOut,NA()))

 

[Rnkhch]

Hi Anthony, thanks much! This is fabulous! I noticed one little thing that is puzzling me. I would need to use the FILTER function to get the input for your function. When the input of your function is changed to a FILTER or a range that corresponds to a FILTER output (rather than cell by cell dragging), then the function doesn't work. Any chance you can fix that? Here is an example XL2BB in which I duplicated the top range of cells at the bottom through a FILTER. In D17, I specified the input as A17#:

Book1
	A	B	C	D	E
1	Input	Desired output		Formula Out
2	Day 17	N/A		#N/A
3	Day 1 - 0-60 min Post	0-60 Min		0-60 Min
4	Day 1 - pre	Pre		Pre
5	Day 1 - 7 Hr Post	1-7 Hr		7 Hr
6	Day 1 - pre(visit1)	Pre		Pre
7	DAY 1(VISIT 1) - 2-4 hr Post	2-4 Hr		2-4 Hr
8	DAY 1(VISIT 2) - 0-1 Hr Post(only visit 2)	0-1 Hr		0-1 Hr
9	Baseline -	Baseline		Baseline
10	DAY 33(VISIT 9) -	N/A		#N/A
11	Day12(baseline)	Baseline		Baseline
12	Day 11 - 7Hr Post	7 Hr		7 Hr
13	DAY 23 - 6 - 8 hr Post	6-8 Hr		6 - 8 Hr
14
15	Day 1 - (0-60 min Post)			(0-60 Min
16
17	Day 17			#N/A
18	Day 1 - 0-60 min Post			#N/A
19	Day 1 - pre			Pre
20	Day 1 - 7 Hr Post			#N/A
21	Day 1 - pre(visit1)			Pre
22	DAY 1(VISIT 1) - 2-4 hr Post			#N/A
23	DAY 1(VISIT 2) - 0-1 Hr Post(only visit 2)			#N/A
24	Baseline -			Baseline
25	DAY 33(VISIT 9) -			#N/A
26	Day12(baseline)			Baseline
27	Day 11 - 7Hr Post			#N/A
28	DAY 23 - 6 - 8 hr Post			#N/A
29
Sheet1

Cell Formulas
Range	Formula
D2:D13,D15	D2	=LET(sStr,A2,aStr,UPPER(TEXTAFTER(sStr,"-")),kW,TEXTSPLIT( UPPER("min,hr,pre,baseline"),,","),LFor,TEXTJOIN("",TRUE, IFERROR(IF(FIND(kW,aStr)>0,kW,""),"")),pOut,IFERROR(IF(FIND("BASELINE",UPPER(sStr))>0,"Baseline",""),TRIM(TEXTBEFORE(aStr,LFor)&" "&PROPER(LFor))),IF(LEN(pOut)>2,pOut,NA()))
A17:A28	A17	=FILTER(A2:A13,1)
D17:D28	D17	=LET(sStr,A17#,aStr,UPPER(TEXTAFTER(sStr,"-")),kW,TEXTSPLIT( UPPER("min,hr,pre,baseline"),,","),LFor,TEXTJOIN("",TRUE, IFERROR(IF(FIND(kW,aStr)>0,kW,""),"")),pOut,IFERROR(IF(FIND("BASELINE",UPPER(sStr))>0,"Baseline",""),TRIM(TEXTBEFORE(aStr,LFor)&" "&PROPER(LFor))),IF(LEN(pOut)>2,pOut,NA()))
Dynamic array formulas.

In this scenario, your function outputs the pres and baselines, but misses the hours and minutes.

Also in the middle section, there is an input with parentheses and the open parenthesis appears in the output.

Thank you so much!

 

[Anthony47]

=LET(sStr,A17#,aStr,etc etc

Why you used "A17#", that stands for "the expanded output that starts in A17", in the formula you set in D17?
It has to be simply sStr,A17:

=LET(sStr,A17,aStr,etc etc

If you wish to remove the opening parenthesis (example in D15) use SUBSTITUTE to define the value of sStr:

=LET(sStr,SUBSTITUTE(A17,"(",""),aStr,etc etc

Note: this is not an array formula; you set the formula in the first row (setting wich is the value to be decoded, ie "sStr,ItsAddress") and then copy the formula down for the remaining rows.

 

[Peter_SSs]

@Rnkhch
Are you sure that your 'Desired output' column is correct?
Wondering why the day number appears in the expected output in B5 but the day number does not appear in the expected output in B12 when the column A data is virtually identical?

BTW, would it ever be possible that the text in column A could inadvertently include some of the short texts that you are searching for? Examples of the sorts of things I am wondering about would be
DAY 1 0-1 Hr Post(only 2 present) As well as the obvious "hr", this also include "pre"
Day 1 - pre(visit three) This also contains both "hr" and "pre"
If such things are possible, how do you envisage handling those?

 

[Rnkhch]

@Peter_SSs

Are you sure that your 'Desired output' column is correct?

Thanks for catching my typo for B5.

BTW, would it ever be possible that the text in column A could inadvertently include some of the short texts that you are searching for?

I haven't seen that happening yet luckily, but good points

@Anthony47

Why you used "A17#", that stands for "the expanded output that starts in A17", in the formula you set in D17?

I wanted to mimic a FILTER situation. Here is how my actual FILTER looks like, and your function doesn't catch the hours and minutes when I use this FILTER for the input:

=UNIQUE(LET(sStr,FILTER(FMBMcReqDay,(FMBMcGeqSID=IQ2)*(ISNUMBER(SEARCH("sample titer",FMBMcL)))*(ISNUMBER(SEARCH("sputum",FMBMcPeqSpec)))),aStr,UPPER(TEXTAFTER(sStr,"-")),kW,TEXTSPLIT( UPPER("min,hr,pre,baseline"),,","),LFor,TEXTJOIN("",TRUE, IFERROR(IF(FIND(kW,aStr)>0,kW,""),"")),pOut,IFERROR(IF(FIND("BASELINE",UPPER(sStr))>0,"Baseline",""),TRIM(TEXTBEFORE(aStr,LFor)&" "&PROPER(LFor))),IF(LEN(pOut)>2,pOut,NA())))

where the input is this FILTER: FILTER(FMBMcReqDay,(FMBMcGeqSID=IQ2)*(ISNUMBER(SEARCH("sample titer",FMBMcL)))*(ISNUMBER(SEARCH("sputum",FMBMcPeqSpec))))

Basically this filter looks at columnR (about 20,000 rows long) and filters based on columnG=IQ2 and if it finds the term "sample titer" in columnL and the term "sputum" in column P. At the end of the filtering process, it generates an array which roughly in the range of hundreds of elements (big decrease from 20,000), and then I want this array to go into your function to get the hour and minute numbers.

Please let me know if you guys need more information. Thanks

 

[Anthony47]

I don't know how the formula will perform on 20k lines (and I had known that the data set was so long I should have proposed a macro or a user function); at the moment my suggestion is to embed the current formula into an additional IF and copy it down for 20k lines.
So, for line 2

=IF(A2<>"",LET(sStr,A2,aStr,UPPER(TEXTAFTER(sStr,"-")),kW,TEXTSPLIT(UPPER("min,hr,pre,baseline"),,","),LFor,TEXTJOIN("",TRUE, IFERROR(IF(FIND(kW,aStr)>0,kW,""),"")),pOut,IFERROR(IF(FIND("BASELINE",UPPER(sStr))>0,"Baseline",""),TRIM(TEXTBEFORE(aStr,LFor)&" "&PROPER(LFor))),IF(LEN(pOut)>2,pOut,NA())),"")

Modify according your starting line (in 2 positions)

 

[Rnkhch]

Thank you! Today was jury day, so I'm gonna test tomorrow at work and see how it works.

 

[Automatrix]

Power Query is built for cleaning up sloppy data. A series of conditional columns and extractions should be able to solve it.

 

[Rnkhch]

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