extarct numbers with a pattern from alphanumeric string

kaav06 · May 3rd 2016

Hi Everyone,

Can I please have a formula to extract numbers from alphanumeric string. I just need the four numbers in between the texts. They are separated by /. A sample data with desired output is given below.

[TABLE="width: 248"]

[tr]

[TD="width: 330, bgcolor: transparent"]WV R&TConst40/15BathroomRenov-Labour[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]FC33027 KMSCon 05/16 1 x Aircon Labour[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]FC29382OzeCool06/16ColdroomMotorLabour[/TD]

[/tr]

[/TABLE]

[TABLE="width: 330"]

[tr]

[td]

Desired Output
40/15
05/16
06/16

Thank you so much in advance

Kaavya

[/td]

[/tr]

[/TABLE]
[/TD]
[/TR]
[/TABLE]

crushdrinker06 · May 3rd 2016

Re: extarct numbers with a pattern from alphanumeric string

This should work for you =MID(A1,SEARCH("/",A1)-2,5)

sktneer · May 5th 2016

Re: extarct numbers with a pattern from alphanumeric string

Place the following UDF on a Standard Module and then use if like a regular formula on the sheet.

Code

Function ExtractNumbers(Str As Range) As String
Dim RegEx As Object
Dim Match As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "\d+/\d+"
If RegEx.test(Str) = True Then
   Set Match = RegEx.Execute(Str)
   ExtractNumbers = Match(0)
Else
   ExtractNumbers = ""
End If
End Function

Display More

For details, refer to the attached workbook.

crushdrinker06 · May 6th 2016

Re: extarct numbers with a pattern from alphanumeric string

sktneer,

Out of curiosity, what would be the benefit of using the vba instead of the formula I put above. Would the vba you made check if it is a number? would it reduce the number of calculations being done on the sheet? It seems like it would work, I am just trying to learn.

extarct numbers with a pattern from alphanumeric string

Participate now!

Share