User Defined Text Function 1

[mllex]

The following function looks for the text "VALUE" and then extracts the numbers in the text that follow it. Examples:

Bonds
FEDERAL NAT'L MTGE ASSN 25,000 PAR VALUE DTD 10/1/1999 8.5000% 11/15/2013 TAXPAYER ID: 04-2222222 SSB-TRUST CUSTODY DISTRIBUTION VALUE: 8,529.44 STATE STREET A/C #BL3625, FAMILY SERVICE OF ....

Stocks
SYMANTEC CORP COM 1,800 SHARES TAXPAYER ID: 04-2222222 SSB-TRUST CUSTODY DISTRIBUTION VALUE: 135,630.00 STATE STREET A/C #xx9999, FAMILY SERVICE ...

Stock Gifts
INTEL CORP COM 322 SHARES TO BRAD H. CLIENT TAXPAYER ID: 111-11-1111 MADE JANUARY 16, 2002 AT VALUE $10,991.47

Reversed Transaction
XXXXXXXX MTGE LOAN TRUST-SER 1997-1 25,000 PAR VALUE DTD 4/15/1999 7.3500% 5/25/2027 TAXPAYER ID: 04-2222222 REVERSED ENTRY POSTED ON 01/18/2002


Public Function GetValue(ExplText)
Dim Step1 As Integer, Step2 As Integer, Step3 As String, step4 As Integer, step5 As String, MyString As String, MyStringLength As Integer

MyString = "VALUE"
MyStringLength = Len(MyString) + 2 '# characters, plus 2, to get to number
Step1 = WorksheetFunction.Find(MyString, ExplText) 'find position of VALUE in string
Step2 = Step1 + MyStringLength 'take position in step 1 for start of VALUE and add 2 (# characters, plus 2, to get to number)
Step3 = Mid(ExplText, Step2, 255) 'starts from specified character(step2) and goes on for 255 characters
step4 = WorksheetFunction.Find(".", Step3) + 2 'finds decimal and adds 2 to get decimal places
step5 = Left(Step3, step4) 'takes step3 string and provides left # of characters as determined in step 4

GetValue = step5 * 1 'take string from left function and multiply by one to get number

End Function


I want to use the same function for the three types of transactions listed above, to get bond values, stock values, and stock gift values.

I am encountering three problems:

1) the text "PAR VALUE" for bonds preceeds "DISTRIBUTION VALUE"
2) gift values do not read "DISTRIBUTION VALUE" but just "VALUE"
3) if a transaction is reversed, I want to return a value of 0

I could ask our Securities Dept. for consistency in the data entry so that I always had a text string of "DISTRIBUTION VALUE" and not "VALUE", but how do I get around the reversals so that I get a 0 value?

I need to calculate the value of multiple transactions. Any help is very much appreciated.

 

[SkipVought]

Hi,
It seems that is you have "VALUE: " THEN you have a numeric value, otherwise a zero return value.

If that is the case, then this function would work...

Function DistValue(txt As Range) As Double
    Dim sValue As String, sByte As String, bFound As Boolean
    Const DVALUE = "VALUE: "
    sValue = ""
    bFound = False
    For i = 1 To Len(txt)
        If Mid(txt, i, Len(DVALUE)) = DVALUE Or bFound Then
            bFound = True
            sByte = Mid(txt, i + Len(DVALUE), 1)
            Select Case sByte
                Case 0 To 9, ",", "."
                    sValue = sValue & sByte
                Case Else
                    DistValue = sValue
                    Exit Function
            End Select
        End If
    Next
    DistValue = 0
End Function

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[dsb]

or return the currency as a string

Function GetValue(ByVal sSource As String) As String
'ByVal required to allow changing of sSource in this
'routine without changing caller's value

Dim iPos As Integer
Dim sFind As String

sFind = "VALUE"
iPos = InStr(UCase$(sSource), sFind)
If iPos > 0 Then
sSource = Mid$(sSource, iPos + 1)
iPos = InStr(sSource, "$&quot
If iPos > 0 Then
sSource = Mid$(sSource, iPos)
iPos = InStr(sSource, " &quot
On Error GoTo handle_error
If iPos > 0 Then
GetValue = Left$(sSource, iPos - 1)
Else
GetValue = sSource
End If
End If
End If
Exit Function

End Function

 

[mllex]

This is AWESOME! Thank you ever so much. I will try it out on my laptop on the way home tonight!

I really appreciate your efforts on this.

 

[SkipVought]

So....

Which solution are you labeling as AWESOME? Skip,
metzgsk@voughtaircraft.com

 

[dsb]

lets see where the STAR shows up....

 

[mllex]

Anything I can get to work. I am a beginner, so when I see the syntax spelled out in a logical way, it really is a gift.

I have tried both...but I get #Value. I am still testing the functions with message boxes. I will come back to you though. Stand by!! Thank you!!

One glitch is that with securities delivered out the string reads DISTRIBUTION VALUE:

When stock gifts are delivered out the text reads simply VALUE (note - no colon)

I cannot just search for the word VALUE, as this creates problems when the text PAR VALUE appears for bond transactions.

?


Also, I'm having errors if the text does not contain the word VALUE, which I need to find a work around, so I can get a total on multiple transactions of this type.

 

[SkipVought]

BKDiva,

Check my solution again! I did NOT simply use "VALUE". I used "VALUE: " (see my comment)

I did a cut and paste of my function from tek-tips to my vba, copied each of your 4 examples to my worksheet, and ran the function with this as my results...

$8,529.44
$135,630.00
$-
$-

So, where is the problem? Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

BKDiva,
You last comment is disturbing me. A function can only return a single value. What do you mean by...
"so I can get a total on multiple transactions of this type."

Are you wanting to total within the function or where? Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

So, if you really need to SUM all the DISTRIBUTION VALUES and return the SUM then here it is...

Function DistValue(txt As Range) As Double
    Dim sValue As String, sByte As String, bFound As Boolean, nValue
    Const DVALUE = "DISTRIBUTION VALUE: "
    sValue = ""
    nValue = 0
    bFound = False
    For i = 1 To Len(txt)
        If Mid(txt, i, Len(DVALUE)) = DVALUE Or bFound Then
            bFound = True
            sByte = Mid(txt, i + Len(DVALUE), 1)
            Select Case sByte
                Case 0 To 9, ",", ".", "$"
                    sValue = sValue & sByte
                Case Else
                    nValue = nValue + sValue
                    sValue = ""
                    bFound = False
            End Select
        End If
    Next
    If nValue > 0 Then
        DistValue = nValue
    Else
        DistValue = 0
    End If
End Function

Hope this helps if I am reading your requirement corectly. Skip,
metzgsk@voughtaircraft.com

 

[mllex]

I haven't had a chance to read through this in full...

I will test it out tomorrow...

But I will be downloading multiple transactions from our intranet. Each transaction has explanation text in which I need to extract the distribution value. Then I need to get a total for the value from each transaction. So, I may have 50 transactions in one account. I will need to use this function to get the distribution value from 50 different strings of explanation text, and then sum these 50 values.

 

[mllex]

Skip -

How do I get 10,991.47 from the third transaction? It just reads VALUE, not VALUE:

The fourth txn result you have posted is perfect. I am getting #value. You guys are way ahead of me, but I will work on this tomorrow on the train and see if I can interpret all of your code. Thank you very, very much.

 

[dsb]

sounds like you have no control over the exact syntax of the input.....

this is common when dealing with web pages....

you need to be able to enumerate each format that you may see
So Far, you have listed the following "markers"
VALUE: followed by $x,xxx.xx
VALUE, followed by $x,xxx.xx

You need to check for each of these and also note that if you get a message without either, that there may be a new format that you need to list....

Just use the code that has been presented, but put a jacket around it
Function bFindThisValue (sValue as string, _
dblValue as double) as boolean
bFindThisValue = False
....use the code given by Skip, replacing the
DVALUE by sVALUE
....if you find sVALUE, then
bFindThisValue=True
dblValue = ...get after the $...
exit function
endif
end function

then
call this for each syntax that may be in the message
if bFindThisValue("VALUE:",dblValue) then
....
elseif bFindThisValue("VALUE,",dblValue) then
....
endif

dsb

 

[mllex]

Skip -

The problem I am having seems to be with the sByte formula.

sByte = Mid(txt, i + Len(DVALUE), 1

While the sample text appears to have one character between DISTRIBUTION VALUE: and the number, this can actually be any # of characters, depending on the numerical value in the string. The long string of explanation text is actually multiple strings of text that have been concatenated together. I think I need to find the decimal place in the number and add two for the decimals to actually get the complete string, and then use a left function? The formula below works, but I obviously cannot give this to users.

=LEFT(MID(C2,SEARCH("DISTRIBUTION VALUE",C2)+20,255),SEARCH(".",MID(C2,SEARCH("DISTRIBUTION VALUE",C2)+20,255))+2)*1


My apologies for being so limited in VBA. Can you explain to me what is happening in the Select Case statement? I have used Select Case before, but not like this. And, I understand what bFound is doing, I just don't get how Excel comprehends "Or bFound" in

If Mid(txt, i, Len(DVALUE)) = DVALUE Or bFound Then

And to dsb...

Thank you too for your comments. At first read, that is over my head, but I can study it and see what I can come up with.

I think I could perhaps resolve the difference between "VALUE" AND "VALUE:" by just asking the data entry people to put in the ":" after the word "VALUE" in our gift transactions. Then I can just search universally for "VALUE:" This way PAR VALUE is not an issue and I can extract a number for our stock gift transactions, which contain the text "VALUE", but not "DISTRIBUTION VALUE".

Many thanks to you both. If you have further suggestions, they would be much appreciated. You guys are amazing.

And - to further reveal my "newbieness"...I am embarrassed to ask this, but how do you give a star? I can't find it. ?

 

[SkipVought]

Sorry,
I missread your requirement. The only thing you want to ignore are reversed. So see if this solves the immediate problem. If you have control over the input, then have them add a colon for transaction that you want to include and modify DVALUE to be "VALUE: ".

Let's address each of the items in your posting...
1.sByte = Mid(txt, i + Len(DVALUE), 1) - what you could do here is once "VALUE: " is found, start looking for Case sByte "0" to "9". When that happens, then you start building sValue.

2. Select Case Statement - Check the VBA help. But it's a better way of accomplishing If...Then...ElseIf...

3. bFound is defined as Boolean. When an experssion is evaluated in an If statement, basically what happens is the experssion is evaluated as either TRUE or FALSE. For instance you might state...

If A = B Then
'do something
Else
'do somethingelse
End If
[code]
When A equals B the expression is evaluated to be TRUE, otherwise FALSE.  So since a Boolean is either TRUE or FALSE, why would you state...
[code]
If bFound = TRUE Then...

That's like saying TRUE equals TRUE.

4. To award a STAR for a post that is a helpful or expert post, select the hyperlink in the lower left-hand corner of that post and follow the bouncing ball.

Function DistValue(txt As Range) As Double
    Dim sValue As String, sByte As String, bFound As Boolean, nValue
    Const DVALUE = "N VALUE: "
    Const AVALUE = "AT VALUE "
    sValue = ""
    nValue = 0
    bFound = False
    For i = 1 To Len(txt)
        If Mid(txt, i, Len(DVALUE)) = DVALUE Or _
            Mid(txt, i, Len(AVALUE)) = AVALUE Or _
            bFound Then
            bFound = True
            sByte = Mid(txt, i + Len(DVALUE), 1)
            Select Case sByte
                Case 0 To 9, ",", ".", "$"
                    sValue = sValue & sByte
                Case Else
                    nValue = nValue + sValue
                    sValue = ""
                    bFound = False
            End Select
        End If
    Next
    If nValue > 0 Then
        DistValue = nValue
    Else
        DistValue = 0
    End If
End Function

Skip,
metzgsk@voughtaircraft.com

 

[mllex]

Thank you, Skip -

You are very kind to look at this for me, and it will most certainly be of significant help to my peers.

I appreciate the help immensely, and will proceed to work through your latest fine example.

 

[SkipVought]

BKDiva,
Never be afraid to ask questions of be a newbie. There is yet ALOT os stuf that I do not know about Excel and VBA. My peers are very helpful enlightening me from time to time.

Glad to be of help Skip,
metzgsk@voughtaircraft.com