Deleting text from right side of an individual cell

[flyfisher15]

I'm attempting to clear text out of the right hand side of a cell. The cell looks like this.


"431: were staged"

The numerical value in this case 431 can be as large as 6 digits or as small as 2. I need to delete the ": were staged" text off the number.

I've tried using

[VBA]Dim x As String
Dim y As String
Dim z As String

Dim trimstring

x = Range("b1")


y = Right(x, 13)

z = x - y

End Sub[/VBA]

without success

I've also tried the .characters function but I can't get it to count from the right.

Any help would be much appreciated!

[Kenneth Hobs]

[VBA]
Function NumberPart(aString As String) As Long
Dim s As String, i As Integer, mc As String
For i = 1 To Len(aString)
mc = Mid(aString, i, 1)
If Asc(mc) >= 48 And Asc(mc) <= 57 Then s = s & mc
Next i
NumberPart = CLng(s)
End Function

Sub Test_Numberpart()
Dim s As String
s = "A5BC123"
Debug.Print s, NumberPart(s)
End Sub[/VBA]

[flyfisher15]

Thanks so much for your help Kenneth!! It worked great!

[VBA]Function NumberPart(aString As String) As Long
Dim s As String, i As Integer, mc As String
For i = 1 To Len(aString)
mc = Mid(aString, i, 1)
If Asc(mc) >= 48 And Asc(mc) <= 57 Then s = s & mc
Next i
NumberPart = CLng(s)
Range("a2") = s
End Function

Sub Test_Numberpart()
Dim s As String
s = Range("a1")
Debug.Print s, NumberPart(s)


End Sub[/VBA]

[mbarron]

You can use the Val() function to extract the number from the beginning of a string:
[VBA]
Function numPart(strInput As String) As Long
numPart = Val(strInput)
End Function[/VBA]