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.

 

[Pank]

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

View attachment 87261

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

Hi Joe,

following your instructions, I managed to apply Jec's function and it works.
I really can't thank enough all members of this thread.
If it's not too much to ask, is there a way for the function to display no result, i.e. leave the cell as blank (null), in case there is no data in cell A1? Currently it displays zeros.

Thank you again

 

[Peter_SSs]

is there a way for the function to display no result

You could try this version.
You may want to include "dpm" for completeness but note that "dpm" is not required in the .Pattern line since any number followed by dpm would already get picked when looking for a number followed by d. (See ** note below)

Function getnum(s As String) As Variant
  getnum = vbNullString
  With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(?=d|x|kt|km)"
    If .Test(s) Then getnum = Val(.Execute(s)(0))
  End With
End Function

Pank.xlsm
	A	B
1	buy apples 20d november 2022	20
2	winter 2021 buy oranges 27dpm	27
3	No number to extract here
4
5	10x box sell oranges autumn 2022	10
6	sell pears 120kt autudmn 2022	120
Extract Number

Cell Formulas
Range	Formula
B1:B6	B1	=getnum(A1)

** That does beg the question though: Could your data have a number followed by d and any other letter(s) apart from "dpm"?
For example, could you have
winter 2021 buy oranges 27def
and, if so, what should the function return?

 

[Pank]

You could try this version.
You may want to include "dpm" for completeness but note that "dpm" is not required in the .Pattern line since any number followed by dpm would already get picked when looking for a number followed by d. (See ** note below)

Function getnum(s As String) As Variant
  getnum = vbNullString
  With CreateObject("VBScript.RegExp")
    .Pattern = "\d+(?=d|x|kt|km)"
    If .Test(s) Then getnum = Val(.Execute(s)(0))
  End With
End Function

Pank.xlsm
	A	B
1	buy apples 20d november 2022	20
2	winter 2021 buy oranges 27dpm	27
3	No number to extract here
4
5	10x box sell oranges autumn 2022	10
6	sell pears 120kt autudmn 2022	120
Extract Number

Cell Formulas
Range	Formula
B1:B6	B1	=getnum(A1)

** That does beg the question though: Could your data have a number followed by d and any other letter(s) apart from "dpm"?
For example, could you have
winter 2021 buy oranges 27def
and, if so, what should the function return?

Hi Peter,
no cases of number followed by letters except d or dpm. Thus, all works perfectly now.
I am truly grateful for all the help.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

no cases of number followed by letters except d or dpm.

I'm wondering then if the task could be simplified to find a number followed immediately by any (lower case) letter(s)?
Not that it would probably make a lot of difference but if that was the case you would not need to individually specify those following sets of characters in the pattern line.

Function getnum(s As String) As Variant
  getnum = vbNullString
  With CreateObject("VBScript.RegExp")
'    .Pattern = "\d+(?=d|x|kt|km)"
    .Pattern = "\d+(?=[a-z])"
    If .Test(s) Then getnum = Val(.Execute(s)(0))
  End With
End Function

 

[Pank]

You're welcome. Glad we could help. Thanks for the follow-up.


I'm wondering then if the task could be simplified to find a number followed immediately by any (lower case) letter(s)?
Not that it would probably make a lot of difference but if that was the case you would not need to individually specify those following sets of characters in the pattern line.

Function getnum(s As String) As Variant
  getnum = vbNullString
  With CreateObject("VBScript.RegExp")
'    .Pattern = "\d+(?=d|x|kt|km)"
    .Pattern = "\d+(?=[a-z])"
    If .Test(s) Then getnum = Val(.Execute(s)(0))
  End With
End Function

Thank you once more. Will implement the revised function.