Help

[JCDalrymple]

I have a challenge for you...


I have been sent a file in which there are 21 columns and in the 21st
column there's approximately 20 seperate pieces of information. In the
21st column I need to get a piece of information that is generally in
the format of XXX XXXXXXX XX (X's = numbers). The problem lies that
this piece of info is in a different part of the column, i.e. sometimes
last, sometimes 5th, sometimes 11th, etc. and I need that info in a
column of it's own. Besides the format it is the only piece of info
that is all numeric. Below is an example of one of the cells in which
I need the info. Just so I don't get fired, because I really have no
idea what this info is, I have replaced the numbers with X's and some
of the letters with astericks. The piece of info I need in this cells
follows "*DB, *L*: ".


**F XXXXXX **S****UNG I**LQU*S* XXXX **L*COM I**LI*,D**MFONDS *DB, *L*:
XXX XXXXXXX XX, XXXX,XX *U*O + XXXX,XX ZINS*N *BZGL. X,XX G*B _OU* **F:
XXXWOXXXXXXXXXXX _


Any help you all could provide would be greatly appreciated.

 

[Guest]

1) 'I'd appreciate your help' will get a lot more help from a volunteer than
' I have a challenge for you...'. We aren't keeping score and playing games.
We are trying to help people.
2) 'Help' is not a very informative description of your problem. Be more
clear next time.
3) You need a User-Defined-Function. I put one together for you called
GetData.
To use it, assuming Col 1 is Col A etc and you wish to reference data in
U1, put the formula in V1 as... =GetData(U1)

'/====================================================/
' Function Purpose: find information with a particular format
'
Public Function GetData(Select_Cell As Range) As String
Dim blnTF As Boolean
Dim iLength As Integer, i As Integer
Dim iSelectLength As Integer
Dim strResult As String

On Error Resume Next

'initiate variables
GetData = ""
iLength = Len("### ####### ##")
iSelectLength = Len(Select_Cell.Value)
blnTF = False

'check if selected cell is a string
If TypeName(Select_Cell.Value) <> "String" Then
GoTo exit_Function
End If

'check if string is long enough for review
If iSelectLength < iLength Then
GoTo exit_Function
End If

'look for pattern
For i = 1 To iSelectLength
If iSelectLength - i + 1 >= iLength Then
strResult = Mid(Select_Cell.Value, i, iLength)
If Len(Trim(Str(Val(Left(strResult, 3))))) = 3 Then
If Mid(strResult, 4, 1) = " " Then
If Len(Trim(Str(Val(Mid(strResult, 5, 7))))) = 7 Then
If Mid(strResult, 12, 1) = " " Then
If Len(Trim(Str(Val(Right(strResult, 2))))) = 2 Then
blnTF = True
End If
End If
End If
End If
End If
End If
If blnTF = True Then
Exit For
End If
Next i

If blnTF = False Then
strResult = ""
End If

exit_Function:
On Error Resume Next
GetData = strResult

End Function
'/====================================================/

Good Luck and Hope this helps.