alpha_pinene
New Member
- Joined
- Jan 27, 2022
- Messages
- 9
- Office Version
- 2016
- Platform
- Windows
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
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