In cell €œA1€� I have the word €œNumber 1234€�. I want to put just the €œ1234€� in
column B for every row that is not blank. Can someone show me how to do that
using VBA??

Thanks
Bill

here's one way, just change the range:

Sub test()
Dim cell As Range
For Each cell In Range("A1:A100")
If cell.Value "" Then
cell.Offset(0, 1).Value = Right(cell.Value, _
Len(cell.Value) - InStr(cell.Value, " "))
End If

Next
End Sub

--


Gary


"open a adobe file from a command button"
osoft.com wrote in message
...
In cell "A1" I have the word "Number 1234". I want to put just the "1234" in
column B for every row that is not blank. Can someone show me how to do that
using VBA??

Thanks
Bill

Sub rght()
Range("A1") = "Number 1234"
myVar = Right(Range("A1"), 4)
MsgBox myVar
End Sub

"open a adobe file from a command button" wrote:

In cell €œA1€� I have the word €œNumber 1234€�. I want to put just the €œ1234€� in
column B for every row that is not blank. Can someone show me how to do that
using VBA??

Thanks
Bill

Might as well use the whole thing:

Sub cpyA1rght4()
Dim aRng, lastRow
lastRow = Cells(Rows.Count,1).End(xlUp).Row
Set myRange = Range("A2:A" & lastRow)
For each c in myRange
If Not c Is Nothing Then
aRng = c.Address
Range(aRng).Offset(0, 1) = Right(Range("$A$1").Value, 4)
End If
Next
End Sub

I didn't run it but it should be OK. If not, just post back.

"open a adobe file from a command button" wrote:

In cell €œA1€� I have the word €œNumber 1234€�. I want to put just the €œ1234€� in
column B for every row that is not blank. Can someone show me how to do that
using VBA??

Thanks
Bill

this is a general purpose function that extract number from sttrings

cal with
=GetNum(A1) where A1 is a cell containing a string with numeric dits and
characters.

Function GetNum(Cell As Range)

MyString = Cell.Value
StringLen = Len(MyString)
GetNum = 0
For i = 1 To StringLen

If (Mid(MyString, i, 1) = 0) And (Mid(MyString, i, 1) <= 9) Then

GetNum = (10 * GetNum) + CInt(Mid(MyString, i, 1))


End If

Next i

End Function


"open a adobe file from a command button" wrote:

In cell €œA1€� I have the word €œNumber 1234€�. I want to put just the €œ1234€� in
column B for every row that is not blank. Can someone show me how to do that
using VBA??

Thanks
Bill

That work very nicely

Thanks

"Joel" wrote:

this is a general purpose function that extract number from sttrings

cal with
=GetNum(A1) where A1 is a cell containing a string with numeric dits and
characters.

Function GetNum(Cell As Range)

MyString = Cell.Value
StringLen = Len(MyString)
GetNum = 0
For i = 1 To StringLen

If (Mid(MyString, i, 1) = 0) And (Mid(MyString, i, 1) <= 9) Then

GetNum = (10 * GetNum) + CInt(Mid(MyString, i, 1))


End If

Next i

End Function


"open a adobe file from a command button" wrote:

In cell €œA1€� I have the word €œNumber 1234€�. I want to put just the €œ1234€� in
column B for every row that is not blank. Can someone show me how to do that
using VBA??

Thanks
Bill