EngineFullDetail is the table's field name I want to extract the data from.Show us your table design.
What are:
EngineFullDetail
type????
That is a completely different problem from the first. In the first example you create an array of every word. Search for "Vin" and then return the next word.And I am not getting any result back. Not sure why
Public Function GetAllAfter(strIn As String, AfterDelimiter As String, Optional StopDelimiter As String) As String
GetAllAfter = Split(strIn, AfterDelimiter)(1)
GetAllAfter = Split(GetAllAfter, StopDelimiter)(0)
End Function
Public Sub test()
Debug.Print GetAllAfter("L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC", "Type", "-")
End Sub
Public Function betweenWords(ByVal theString As String, ByVal start_word As String, ByVal end_word As String) As String
Dim i As Integer
i = InStrRev(theString, start_word)
If i <> 0 Then
theString = Mid$(theString, i + Len(start_word))
i = InStr(1, theString, end_word)
If i <> 0 Then
theString = Trim$(Left$(theString, i - 1))
End If
End If
betweenWords = theString
End Function
//this has Validation to check if the "start word" and "end word" exist on the target string.
Public Function betweenWords(ByVal theString As String, ByVal start_word As String, ByVal end_word As String) As String
Dim i As Integer
i = InStrRev(theString, start_word)
If i <> 0 Then
theString = Mid$(theString, i + Len(start_word))
i = InStr(1, theString, end_word)
betweenWords = theString
If i <> 0 Then
theString = Trim$(Left$(theString, i - 1))
betweenWords = theString
End If
End If
End Function
It will not skip. It will teturn the original string.would have skipped over it and come blank
On Error GoTo ErrorHandler
Error_Exit:
Exit Function
ErrorHandler:
MsgBox Error$
Resume Error_Exit
Public Function GetType(strIn As String, star_string as string, end_string as string) As String
On Error GoTo ErrorHandler
GetType = Trim$(Split(strIn, start_string)(1))
GetType = Trim$(Split(GetType, end_string)(0))
Error_Exit:
Exit Function
ErrorHandler:
'MsgBox Error$
Resume Error_Exit
End Function
Public Function GetType(strIn As Variant, star_string as string, end_string as string) As String
strIn = strIn & ""
On Error GoTo ErrorHandler
GetType = Trim$(Split(strIn, start_string)(1))
GetType = Trim$(Split(GetType, end_string)(0))
Error_Exit:
Exit Function
ErrorHandler:
'MsgBox Error$
Resume Error_Exit
End Function
That code works. There is a typo in the first line. star_string needs to be start_string.if there are blank/null fields you need to change your function
so the function will only return blank string and not #Error:
Code:Public Function GetType(strIn As Variant, star_string as string, end_string as string) As String strIn = strIn & "" On Error GoTo ErrorHandler GetType = Trim$(Split(strIn, start_string)(1)) GetType = Trim$(Split(GetType, end_string)(0)) Error_Exit: Exit Function ErrorHandler: 'MsgBox Error$ Resume Error_Exit End Function