VBA to Pull Only Numbers From a Text String with Number In It.

[Guzzlr]

Option Explicit

Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
End Function
 
Sub PullNumbers()
    Range("E2").Select
    ActiveCell.Formula = "=StripChar(A2)"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E8"), Type:=xlFillDefault
    Range("E2:E8").Select
    Range("A1").Select
End Sub

So I'm using the above code to pull numbers from a text string that has numbers mixed in with it.
The problem is if I have a decimal place. The code will still pull the numbers to the right of the decimal and this I do not want.
For example, if I have:

[TABLE="width: 201"]
<tbody>[TR]
[TD="class: xl63, width: 201"]ahrihpxf9c / 1d.123

the code will pull back: 91123

But I only want 91

How can I trunc this number, or use the =LEFT...or something?
Thanks for the help[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Will your text ever have periods (dots that are not decimal points)?

 

[Guzzlr]

Will your text ever have periods (dots that are not decimal points)?

Unfortunately yes...each text may have a decimal point, or it may not. The text strings are random.
Thanks for the help

I just reread your question. The text string is not in the form of a sentence. Each cell contains a mixture of letters and numbers mixed together. Some of the letters/numbers may have a decimal point, or it may not.
At any rate, each data set of letters/numbers will always be confined to one cell.
Thanks

 

[Rick Rothstein]

I just reread your question. The text string is not in the form of a sentence. Each cell contains a mixture of letters and numbers mixed together. Some of the letters/numbers may have a decimal point, or it may not.

That does not address what I was actually after. Could you have text like this and, if so, what would you want as an answer for them...

abc.de93fg3.49

a9bc.de87fg.h65ij

 

[Guzzlr]

That does not address what I was actually after. Could you have text like this and, if so, what would you want as an answer for them...

abc.de93fg3.49

a9bc.de87fg.h65ij

I would not see text like this, with two periods / decimal points. Only one decimal point, and I would be looking for numbers to the left of the decimal point.
Thanks for the help

 

[Rick Rothstein]

I would not see text like this, with two periods / decimal points. Only one decimal point, and I would be looking for numbers to the left of the decimal point.

In that case, I think this function will do what you want...

Function StripChar(Txt As String) As String
  Dim X As Long
  For X = 1 To InStr(Txt & ".", ".") - 1
    If Mid(Txt, X, 1) Like "#" Then StripChar = StripChar & Mid(Txt, X, 1)
  Next
End Function