Extracting Text From A Field (1 Viewer)

jdraw

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Jan 23, 2006
Messages
15,364
Show us your table design.
What are:
EngineFullDetail
type????
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:27
Joined
May 21, 2018
Messages
8,463
And I am not getting any result back. Not sure why
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.
In this problem if you split on "-" you get 3 strings

L6
9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10
4 valve SOHC

None of those strings = "Type" so no value is returned.
You need some different code to do that.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:27
Joined
May 21, 2018
Messages
8,463
Something like this would work
Code:
Public Function GetType(strIn As String) As String
  GetType = Split(strIn, "Type")(1)
  GetType = Split(GetType, "-")(0)
End Function
 

EzGoingKev

Registered User.
Local time
Today, 11:27
Joined
Nov 8, 2019
Messages
178
I guess my understanding of how the function works is incorrect.

Using my example:

L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC

I thought it would pull all text after the word "type" and stop at the second delimiter which I set as "-".
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:27
Joined
May 21, 2018
Messages
8,463
No. But this would be a generic function to pass in where to start and end.
Code:
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
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:27
Joined
Jan 23, 2006
Messages
15,364
I agree with MajP - this is a different issue.Originally we were separating fragments based on a single delimiter. In your latest request, the fragments/string are not all separated by "-". You will have to decide if you want the leading and trailing space OR Trim the result.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:27
Joined
May 7, 2009
Messages
19,169
Code:
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

?betweenwords("L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC", "type", "-")
MaxxForce 10

//this has Validation to check if the "start word" and "end word" exist on the target string.
 

EzGoingKev

Registered User.
Local time
Today, 11:27
Joined
Nov 8, 2019
Messages
178
//this has Validation to check if the "start word" and "end word" exist on the target string.

@arnelgp - thanks for the code.

I do not believe the validation is working correctly. I am running the function on field in a table using BetweenWords([FieldName], "type", "-"). Here are two examples.

Original string -> BetweenWords Result

L6 - 9.3L 570ci DIESEL DI Turbo/Aftercooled/Intercooled type MaxxForce 10 - 4 valve SOHC-> MaxxForce 10
L6 - 5.9L 359ci DIESEL DI Turbocharged vin 6 - 4 valve OHV -> L6 - 5.9L 359ci DIESEL DI Turbocharged vin 6 - 4 valve OHV

Since the second string above does not have the word "type" in it I would have thought the validation process would have skipped over it and come blank. Is my thinking incorrect?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:27
Joined
May 21, 2018
Messages
8,463
change code to
Code:
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
 

EzGoingKev

Registered User.
Local time
Today, 11:27
Joined
Nov 8, 2019
Messages
178
Thanks for the help with this.

So I am at point where I can understand some of the VBA and write a little on my own. If I need to do something I will google it, search here, etc. Sometimes I can find something will do exactly what I need it to do. Sometimes it does not do exactly what I need it to do but I can understand the code enough to modify it so it will. A lot of the stuff I have no clue about but I am learning.

One question I have is that a lot of the code I see has an "Error Handler" part of it. Typically something like this:

Code:
On Error GoTo ErrorHandler

Error_Exit:
    Exit Function

ErrorHandler:
    MsgBox Error$
    Resume Error_Exit

Some code does not. This code did not have it. Is it just personal preference thing or is there a "rule" based on what the code is doing to use an "Error Handler"?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:27
Joined
May 7, 2009
Messages
19,169
then use code on post#24 (no need any validation, meaning if it fails, just return a blank string):
Code:
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

i commented out the Msgbox since the function will be used in query.
you don't want to get Msgbox on each records that fails causing the
query to pause for the msgbox.
 

EzGoingKev

Registered User.
Local time
Today, 11:27
Joined
Nov 8, 2019
Messages
178
OK thanks. I understand a little more now.

So if you put it a query and there is an issue with the data, like a blank field, an error message would pop up for every single instance. This would hold things up. Leaving it out the query runs and you would just get the #Error in the field and not hold things up.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:27
Joined
May 7, 2009
Messages
19,169
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
 

EzGoingKev

Registered User.
Local time
Today, 11:27
Joined
Nov 8, 2019
Messages
178
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
That code works. There is a typo in the first line. star_string needs to be start_string.
 

Users who are viewing this thread

Top Bottom