PDA

View Full Version : Deleting text from right side of an individual cell

flyfisher15
01-20-2012, 01:55 PM
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

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

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
01-20-2012, 02:03 PM
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

flyfisher15
01-20-2012, 02:27 PM
Thanks so much for your help Kenneth!! It worked great!

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

mbarron
01-20-2012, 02:59 PM
You can use the Val() function to extract the number from the beginning of a string:

Function numPart(strInput As String) As Long
numPart = Val(strInput)
End Function