Extract a number before specific letters

Pank

New Member
Joined
Mar 10, 2023
Messages
7
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You could use Regex for this

VBA Code:
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
AB
1buy apples 20d november 202220
2winter 2021 buy oranges 27dpm27
310x box sell oranges autumn 202210
4sell pears 120kt autudmn 2022120
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=jec(A1)
 
Upvote 0
You could use Regex for this

VBA Code:
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
AB
1buy apples 20d november 202220
2winter 2021 buy oranges 27dpm27
310x box sell oranges autumn 202210
4sell pears 120kt autudmn 2022120
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=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?


 
Upvote 0
An option:

Book1
AB
1buy apples 20d november 202220
2winter 2021 buy oranges 27dpm27
310x box sell oranges autumn 202210
4sell pears 120kt autudmn 2022120
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=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))
 
Upvote 0
An option:

Book1
AB
1buy apples 20d november 202220
2winter 2021 buy oranges 27dpm27
310x box sell oranges autumn 202210
4sell pears 120kt autudmn 2022120
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=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
 
Upvote 0
Try:
Excel Formula:
=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)))
 
Upvote 0
Try:
Excel Formula:
=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.
 
Upvote 0
Both the function and formula that JEC provided seems to work perfectly on the sample data you provided:

1678480547145.png


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:
Excel Formula:
=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
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top