Thank you so much. It worked perfectly for me.

"Ron Rosenfeld" wrote:

On Wed, 18 Feb 2009 06:58:01 -0800, 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



Here's another UDF that does not use Regular Expressions:

========================
Option Explicit
Option Compare Binary
Function GetCode(sTxt As String) As String
Const sPattern As String = "[A-Z]#####"
Dim i As Long
For i = 1 To Len(sTxt) - 6
If Mid(sTxt, i, 6) Like sPattern Then
GetCode = Mid(sTxt, i, 6)
Exit Function
End If
Next i
End Function
=================================
--ron