Consider the following User Defined Function:

Function lookit(r As Range) As String
Dim s As String
s = r.Value
Dim outputt(5) As String
iPlace = 0
For i = 1 To Len(s)
ch = Mid(s, i, 1)
If iPlace = 0 Then
If caps(ch) Then
outputt(iPlace) = ch
iPlace = iPlace + 1
End If
Else
If digit(ch) Then
outputt(iPlace) = ch
iPlace = iPlace + 1
If iPlace = 6 Then Exit For
Else
iPlace = 0
End If
End If
Next
lookit = Join(outputt, "")
End Function


Function digit(v As Variant) As Boolean
v2 = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "0")
digit = False
For i = LBound(v2) To UBound(v2)
If v = v2(i) Then
digit = True
Exit Function
End If
Next
End Function


Function caps(v As Variant) As Boolean
Strn = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y ,Z"
v2 = Split(Strn, ",")
caps = False
For i = LBound(v2) To UBound(v2)
If v = v2(i) Then
caps = True
Exit Function
End If
Next
End Function

So, for example, if A1 contained:
87326428A12345lksfasjk
=lookit(A1) would display:

A12345

--
Gary''s Student - gsnu200834


"rushdhih" wrote:

In order to meet client reporting requirements, we download data from our ERP
system into Excel. Our ERP system has limitations and to overcome this we
have codified client budget data. Every transaction description field entered
into the ERP system includes a 6 digit code (Eg:A52101). The problem I am
encountering in the downloaded data is there are system generated text and
other spurious text that creeps in during the conversion process. The code
appears sometime in the left, right or center of the transaction description
together with other text.

I need a formula that will check through the transaction description and
extract only the first occurence of the code.

The pattern of the code - Alpha + 5 Numeric character (no spaces)


Thank you in advance for your help