Hi,
I am trying to pull the numbers out of rows of text. The number placement varies, although it is always before a "d" or "dpm" or "x" or "kt" or "km" text.
I've searched older postings and I think the most suitable for my case is the below (found in thread "How to extract a number before a specific word in a string of text")
=0+(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,"d",REPT(" ",99)),99))," ",REPT(" ",99)),99)))
but can't find a way/don't have the knowledge to add the rest of the letters (dpm, x, kt, km)
Examples of text:
buy apples 20d november 2022
winter 2021 buy oranges 27dpm
10x box sell oranges autumn 2022
sell pears 120kt autumn 2022
The needed numbers to extract are 20, 27, 10, 120 respectively.
Thank you very much in advance for any help.
I am trying to pull the numbers out of rows of text. The number placement varies, although it is always before a "d" or "dpm" or "x" or "kt" or "km" text.
I've searched older postings and I think the most suitable for my case is the below (found in thread "How to extract a number before a specific word in a string of text")
=0+(TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,"d",REPT(" ",99)),99))," ",REPT(" ",99)),99)))
but can't find a way/don't have the knowledge to add the rest of the letters (dpm, x, kt, km)
Examples of text:
buy apples 20d november 2022
winter 2021 buy oranges 27dpm
10x box sell oranges autumn 2022
sell pears 120kt autumn 2022
The needed numbers to extract are 20, 27, 10, 120 respectively.
Thank you very much in advance for any help.