help converting complicated formula into VBA

[tboetticher]

If anyone can help me, I would appreciate the help. I am looking at creating a new function in excel. The function would perform the below action.

function Extract_text(Lookup_Value, Within_Text, Number of Characters)

Substitute(if(iserror(search("Lookup_value","within_text")),"",mid("Within_Text",search("Lookup_value", "Within_text")),number of Characters)),"","")

 

[BrianMH]

Can I ask why you don't want to use the function? Built in functions are faster than VBA.

 

[tboetticher]

Hey Brian,

Thanks for responding. I am looking to convert this formula into a function in order to speed up my business process and decrease the amount of time I spend re writing the formula or modifying the formula. I am also going to use the customer function to help me build several other functions that are much longer and more time intensive.

 

[BrianMH]

Ok. Instead of the formula can you give us an idea of what you will input and what you'd like to be returned?

 

[tboetticher]

I use this formula to retrieve information from within large strings in a near by cell. It uses search to find the particular text I'm looking for ie "Milk" and returns a predetermined number of characters.

 

[BrianMH]

You can shorten that formula.

=IF(ISNUMBER(SEARCH("lookup","within_text")),MID("within_text",SEARCH("lookup","within_text"),Num of Chars),"")

If you really want to do it through VBA then here is the function.

Public Function ExtractText(Lookup_Value As String, Within_Text As String, Chars As Integer)
    Dim s As String
    If Not UCase(Within_Text) Like "*" & UCase(Lookup_Value) & "*" Then
        ExtractText = ""
        Exit Function
    Else
        ExtractText = Mid(Within_Text, InStr(Within_Text, Lookup_Value), Chars)
    End If


End Function

 

[tboetticher]

This works perfect. Thank you so much.