Extract a number before specific letters

[Pank]

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.

 

[JEC]

You could use Regex for this

Function jec(cell As String) As Long
 With CreateObject("vbscript.regexp")
   .Pattern = "\d+(?=(d|dpm|x|kt|km))"
   If .test(cell) Then jec = .Execute(cell)(0)
 End With
End Function

Book1
	A	B
1	buy apples 20d november 2022	20
2	winter 2021 buy oranges 27dpm	27
3	10x box sell oranges autumn 2022	10
4	sell pears 120kt autudmn 2022	120
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=jec(A1)

 

[Pank]

You could use Regex for this

Function jec(cell As String) As Long
 With CreateObject("vbscript.regexp")
   .Pattern = "\d+(?=(d|dpm|x|kt|km))"
   If .test(cell) Then jec = .Execute(cell)(0)
 End With
End Function

Book1
	A	B
1	buy apples 20d november 2022	20
2	winter 2021 buy oranges 27dpm	27
3	10x box sell oranges autumn 2022	10
4	sell pears 120kt autudmn 2022	120
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=jec(A1)

Hi Jec,

thank you for taking the time to reply.
Unfortunately, I don't know how to use Regex. Any case this can be done via a conditional formula?

 

[JEC]

An option:

Book1
	A	B
1	buy apples 20d november 2022	20
2	winter 2021 buy oranges 27dpm	27
3	10x box sell oranges autumn 2022	10
4	sell pears 120kt autudmn 2022	120
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=MAX(IFERROR(--MID(A1,MAX(IFERROR(SEARCH({0;1;2;3;4;5;6;7;8;9}&{"d","dpm","x","kt","km"},A1),0))+1-{1,2,3,4},{1,2,3,4}),0))

 

[Pank]

An option:

Book1
	A	B
1	buy apples 20d november 2022	20
2	winter 2021 buy oranges 27dpm	27
3	10x box sell oranges autumn 2022	10
4	sell pears 120kt autudmn 2022	120
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=MAX(IFERROR(--MID(A1,MAX(IFERROR(SEARCH({0;1;2;3;4;5;6;7;8;9}&{"d","dpm","x","kt","km"},A1),0))+1-{1,2,3,4},{1,2,3,4}),0))

I am getting zeros, not the numbers

 

[snjpverma]

Try:

=0+(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1,FIND("d",A1)-1)),"dpm",REPT(" ",99)),"x",REPT(" ",99)),"kt",REPT(" ",99)),"km",REPT(" ",99)),99)))

 

[Pank]

Try:

=0+(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(A1,FIND("d",A1)-1)),"dpm",REPT(" ",99)),"x",REPT(" ",99)),"kt",REPT(" ",99)),"km",REPT(" ",99)),99)))

It didn't work, I'm getting a #VALUE . Thank you very much though, Snjpverma.

 

[Joe4]

Both the function and formula that JEC provided seems to work perfectly on the sample data you provided:

So if something is not working, maybe you are not applying the solutions properly.

Regarding the function, you do not need to know the first thing about RegEx in order to use. Just paste his function into a new VBA module in your workbook, and then you can use it like any other function, i.e. place this formula in cell B2:

=jec(A2)

and copy down for the rest of the rows.

And the function works properly too.
If that is not returning the expected results, then I suspect that either:
- you have not updated the formula to look at the cell your data is in
or
- you have not represented your sample data accurately

If you cannot get it to work, please show us an example of it not working where we can see:
- the exact data
- the exact cell address your data is placed in
- the exact formula you are using
- the result you are getting

 

[Fluff]

If you are trying it in 2016, rather than 365 you will need to confirm the formula jec posted in post#4 with Ctrl Shift Enter, rather than just Enter.

 

[Tom.Jones]

Maybe your regional settings...
Try:
=MAX(IFERROR(--MID(A1;MAX(IFERROR(SEARCH({0;1;2;3;4;5;6;7;8;9}&{"d"\"dpm"\"x"\"kt"\"km"};A1);0))+1-{1;2;3;4};{1;2;3;4});0))