Truncating cells
For numbers beginning, middle or end regardless of spaces:-
Option Explicit
Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
.Pattern = "d+|\d+.\d+"
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, 1) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
End Sub
Mike
"Simon" wrote:
I have a column of cells formatted as text containing a number and text i.e:
123.123 text text text
I need some way of truncating the cell to only show the number for
calculation purposes. Any Ideas?
|