One way:

Dim rCell As Range
Dim rDest As Range
Dim nPos As Long
Dim nEnd As Long
Dim sTemp As String
Set rDest = Worksheets("Sheet2").Range("A1")
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
sTemp = rCell.Text
If sTemp Like "*A###*" Then
nPos = InStr(1, sTemp, "A")
Do While nPos < 0
If Mid(sTemp, nPos, 4) Like "A###" Then
nEnd = InStr(nPos, sTemp, " ")
If nEnd = 0 Then nEnd = 32767
rDest.Value = Mid(sTemp, nPos, nEnd - nPos)
Set rDest = rDest.Offset(1, 0)
Exit Do
End If
nPos = InStr(nPos + 1, sTemp, "A")
Loop
End If
Next rCell

In article .com,
"trammy" wrote:

In the excel file I have:

123 A5679 REF
A111 ACC
BLOCK 545 A2456
COPY Abbc23

I would like to search for string beginning with "A" and at least 3
numbers following after letter A. Then I want to copy that string to
the new cell.
For example, the first cell, the string I want is A5679
second cell, A111
third cell, A2456
forth cell, there is no string I want
because after letter A, it is not a number.

So in the new sheet, I should have
A5679
A111
A2456

Thanks a lot,

Tammy