Can the VBA LIKE or any similar function return the actual string found using wildcards instead of the T/F bool?

[u144644]

I am trying to search for a string in text using wildcard and returning the actual text found or the start and ending position of string in text.
Since the INSTR function does not use wildcards, can the VBA LIKE or any similar function return the actual string found or location in text using wildcards which I then can search using INSTR or other?

 

[JEC]

Do you have some sample data with expected results?

 

[jkpieterse]

You could consider using a reference to the scripting runtime library, it has a regular expressions module you might use? See: The Complete Guide to Regex in VBA - VBA and VB.Net Tutorials, Education and Programming Services

 

[JEC]

Regular expression will definitely do the job. Not the most simple solution though

 

[u144644]

Do you have some sample data with expected results?

This is an example of the code used where a string "TEXT" content is extracted into 4 separate Result strings.
The problem is the TEXT string content has been modified with additional custom text as defined in TEXT2 and TEXT3.
The Instr function does not accept search strings with wildcards (ie; "Task* Description:") and the LIKE function only returns a Boolean value and not a string location integer value.

--------------------------------------------------------
Dim Text, Text1, Text2, Text3 As String
Dim SrcString1, SrcString2, SrcString3, SrcString4 As String
Dim SrcStrLoc1, SrcStrLoc2, SrcStrLoc3, SrcStrLoc4 As Integer
Dim StrResult1, StrResult2, StrResult3, StrResult4 As String


Text1 = "Task Description: This is my task description. Task Type: This is my task type. Task Action: This my task action. Task Result: This is my task result. "
Text2 = "Task1 Description: This is my task description. Task1 Type: This is my task type. Task1 Action: This my task action. Task1 Result: This is my task result. "
Text3 = "Task Blue Description: This is my task description. Task Blue Type: This is my task type. Task Blue Action: This my task action. Task Blue Result: This is my task result. "

Text = Text1

SrcString1 = "Task Description:"
SrcString2 = "Task Type:"
SrcString3 = "Task Action"
SrcString4 = "Task Result"



SrcStrLoc1 = InStr(1, Text, SrcString1)
SrcStrLoc2 = InStr(1, Text, SrcString2)
SrcStrLoc3 = InStr(1, Text, SrcString3)
SrcStrLoc4 = InStr(1, Text, SrcString4)



StrResult1 = Mid(Text, SrcStrLoc1 + Len(SrcString1), SrcStrLoc2 - Len(SrcString1) - 1)
' = "This is my task description."
StrResult2 = Mid(Text, SrcStrLoc2 + Len(SrcString2), SrcStrLoc3 - SrcStrLoc2 - Len(SrcString2) - 1)
' = "This is my task type."
StrResult3 = Mid(Text, SrcStrLoc3 + Len(SrcString3), SrcStrLoc4 - SrcStrLoc3 - Len(SrcString3) - 1)
' = "This my task action."
StrResult4 = Mid(Text, SrcStrLoc4 + Len(SrcString4), Len(Text) - SrcStrLoc4 - Len(SrcString4))
' = "This is my task result."

 

[JEC]

Can you post your actual data? With the results next to the strings

 

[u144644]

Can you post your actual data? With the results next to the strings

For simplicity, this is the data and results I'm working with. The code previously posted works with TEXT1 but not TEXT2 or TEXT3.
Is there any other Function or method I can use in my code to get the same results for all 3 Data rows?

Num	Data	Result1	Result2	Result3	Result4
TEXT1	Task Description: This is my task description. Task Type: This is my task type. Task Action: This my task action. Task Result: This is my task result.	This is my task description.	This is my task type.	This my task action.	This is my task result.
TEXT2	Task1 Description: This is my task description. Task1 Type: This is my task type. Task1 Action: This my task action. Task1 Result: This is my task result.
TEXT3	Task Blue Description: This is my task description. Task Blue Type: This is my task type. Task Blue Action: This my task action. Task Blue Result: This is my task result.

 

[JEC]

You don't need VBA. Pretty simple with an O365 formula.
I used Char(160) here because your spaces are no-break spaces...

=TEXTAFTER(TEXTSPLIT(B1,"."),":"&CHAR(160),,,,"")

 

[u144644]

You don't need VBA. Pretty simple with an O365 formula.
I used Char(160) here because your spaces are no-break spaces...

=TEXTAFTER(TEXTSPLIT(B1,"."),":"&CHAR(160),,,,"")

Nice function to know about but this would only work if the data only had 1 delimited string to extract. The text data I am working with there is a possibility of 1 to 10 delimited results in each data row and the order of those results can vary o its important to extract the left side of the delimiter to determine what result is extracted. So, I am still searching for a built-n or custom function to search for a string using wildcards similiar to the LIKE operator but returns the string found not a Boolean if found.