Extracting numbers from text string and including units (mm)

[alpha_pinene]

Hi all!
I am working on a project in which I am pulling data from pathology reports regarding skin cancer. The text reports are quite lengthy and contain many numbers (i.e., dates, cancer stage, and the number I am interested in, which is called Breslow thickness). I have managed to use formulas to condense the text into a small segment surrounding my word of interest ("Breslow") with 12 characters before the word Breslow and 75 characters after the word. ($S$1 corresponds to a cell with the word "Breslow.")

The string of 1's is because I could only figure out how to get characters to the LEFT of "Breslow" to show up by adding more numbers in that set of parentheses (which there is probably a better way and I would love ot hear it if so).

Then, I was able to get the number to the front of another column using the below formula:

But herein lies my problem. Sometimes, my number of interest (which typically ranges from 0.1-4 mm) isn't the first number in the string of text, and I will capture a date or a time (like 9/17 or 4:00:00AM), which is problematic, because I need to average all of the Breslow thicknesses and I'm getting a significant number of cells that don't capture the right number.

I thought if I could develop a formula that would search for a number PLUS the units (like 0.4mm or 0.4 mm, though there are variations in which cells do and don't have spaces which could complicate things), then I could be able to avoid getting the dates/times.

I am at my wit's end and would really appreciate any advice that can be offered.

Thank you so much!

alpha_pinene

 

[alpha_pinene]

Alternative formula:

Book1
	A	B
1	Full text examples
2	Pathology report: Date: 01/27/2022, Provider: x physician, biopsy type: shave, body location: right upper extremity, diagnosis: melanoma. 1/27/2022 Breslow thickness of 0.4 mm, ulceration not identified, mitotic figures <1/mm2, regression absent.	0.4
3	Pathology report: Date: 01/27/2022, Provider: x physician, biopsy type: shave, body location: right upper extremity, diagnosis: melanoma. 1/27/2022 Breslow thickness of 0.4mm, ulceration present, mitotic figures 12/mm2, regression absent.	0.4
4	Pathology report: Date: 01/27/2022, Provider: x physician, biopsy type: shave, body location: right upper extremity, diagnosis: melanoma. 1/27/2022 Breslow thickness: (0.4mm), ulceration present, mitotic figures 12/mm2, regression absent.	0.4
5	maximum tumor (Breslow) thickness in millimeters: 0.7 millimeters	0.7
6	Breslow of 2.1mm along bulbar conjunctiva	2.1
7	0.75 mm in Breslow thickness, located on the back	0.75
8	4 mm in depth located on the left foot	4
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=AGGREGATE(14,6,MID(SUBSTITUTE(A2,"milli","mm"),IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(SEARCH("?.?mm",SUBSTITUTE(A2,"milli","mm")),SEARCH("?.?mm",SUBSTITUTE(A2,"milli","mm"))),SEARCH("?.??mm",SUBSTITUTE(A2,"milli","mm"))),SEARCH("?.???mm",SUBSTITUTE(A2,"milli","mm"))),SEARCH("? mm",SUBSTITUTE(A2,"milli","mm"))),SEARCH("?mm",SUBSTITUTE(A2,"milli","mm"))),{1,2,3,4})+0,1)

After going through all of my data, it actually seems that the previous solution ended up missing a few values (could not figure out why) - however, this solution caught every single number that I needed. I am so grateful to both of the posters for helping me solve this, you've saved me tons of time! I am indebted.