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

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
765
Code:
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:

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

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
Will your text ever have periods (dots that are not decimal points)?
 

Guzzlr

Well-known Member
Joined
Apr 20, 2009
Messages
765
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Apr 20, 2009
Messages
765
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
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...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

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
Top