MatthewLane412
New Member
- Joined
- Aug 23, 2017
- Messages
- 24
In my excel file I have column AK. Each cell is filled with strings. These strings contain numeric and non numeric information. An example of a string is "house 7410145689 October 2018". The stings always contain different information. Sometimes they contain a 10 digit number and sometimes they don't. The 10 digit number can move to different places in the string. My End goal is for the column to be left with nothing but either a blank cell or a 10 digit string. Currently I came up with some code that has a 97 percent success rate by using a combination of inStr with replace, code that deletes all non numeric characters, and code that clears the contents of cells that are less than 10 digits. I Found the following code online but I'm having trouble implementing it for Column AK (or at all). So far I have added on Tools -->References-->Microsoft VBScript Regular Expressions 5.5. Any help would be greatly appreciated. Thank you,Matt
Code:
Function Extract(S As String) As String
Dim RE As New RegExp
Dim MyMatches As MatchCollection
Dim MyMatch As Match
RE.pattern = "(?:^|[^0-9]) ([0-9]{10})(?:[^0-9]|$)"
Set MyMatches = RE.Execute(S)
If MyMatches.Count = 0 Then
Extract ""
Else
Set MyMatch=MyMatches(0)
Extract=MyMatch.SubMatches(0)
End If
End Function
