How to find text between 2 words in a field via VBA?

  • Thread starter Thread starter hanski
  • Start date Start date
H

hanski

Hi.

Example:
I have a field which have a following text:
"My first name is Peter and my last name is Johnson and I have a cat"

I have 100 fields like that and I would like to pick up automatically
from the text first names and last names into my other database.

How can I find via VBA those text 'Peter' and *Johnson' and put those
in other database or variable?

The sentence "My first......" is allways starndard.

Thanks a lot for you help!
 
You'd have to write a custom function. Something like:

Function GetText( _
InputString As String, _
FirstWord As String, _
SecondWord As String) As String
Dim lngFirstWord As Long
Dim lngSecondWord As Long

lngFirstWord = _
InStr(1, InputString, FirstWord, vbTextCompare)
If lngFirstWord > 0 Then
lngFirstWord = lngFirstWord + Len(FirstWord)
lngSecondWord = _
InStr(lngFirstWord, InputString, SecondWord, vbTextCompare)
If lngSecondWord > 0 Then
GetText = _
Mid$(InputString, lngFirstWord, (lngSecondWord - lngFirstWord) + 1)
Else
GetText = vbNullString
Else
GetText = vbNullString
End If

End Function

(Hopefully there's no word-wrap in that...)
 
Are you saying that the first name you seek is ALWAYS preceeded by "My first
name is ", and the last name you seek is ALWAYS preceeded by " and my last
name is "?

Is there never a typographic error?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Are you saying that the first name you seek is ALWAYS preceeded by "My first
name is ", and the last name you seek is ALWAYS preceeded by " and my last
name is "?

Is there never a typographic error?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.











- Näytä siteerattu teksti -
Click to expand...

Yes, they are allways like that.

Never errors because the base is allways the same, only names changes.
 
Then Doug's approach should suffice...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Are you saying that the first name you seek is ALWAYS preceeded by "My
first
name is ", and the last name you seek is ALWAYS preceeded by " and my last
name is "?

Is there never a typographic error?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.











- Näytä siteerattu teksti -
Click to expand...

Yes, they are allways like that.

Never errors because the base is allways the same, only names changes.
 
Actually, no. I misread the requirements!

The following is based on the assumption that the names are always preceded
with the word "is" and always followed by the word "and" (a necessary
assumption to handle multii-part names like Mary Lou)

Function GetName( _
InputString As String,
WhichName As String _
) As String

' Expects WhichName to be "First" or "Last"

Dim lngPhraseStart As Long
Dim lngAndStart As Long
Dim strPhrase As String
Dim strName As String

Select Case WhichName
Case "First", "Last"
strPhrase = "my " & WhichName & " name is "
lngPhraseStart = InStr(1, InputString, strPhrase, vbTextCompare)
If lngPhraseStart > 0 Then
lngPhraseStart = lngPhraseStart + Len(strPhrase)
lngAndStart = _
InStr(lngPhraseStart, InputString, "and", vbTextCompare)
If lngAndStart > 0 Then
GetName = _
Mid$(InputString, lngPhraseStart, (lngAndStart - lngPhraseStart
+ 1)
End If
Case Else
End Select

End Function
 
Back
Top