Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help
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. -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|