formula to extract portion of text

[yavahn]

Hi folks
I need a formula to use in a MS Access query to extract a portion of text from a text string, where the portion lies between 2 delimiting characters (#)
The field length is not always the same.
I tried the "MID", "Instr", "LEN" functions, but nothing successful yet

 

[namliam]

what do you have that is not working??

If all else fails make a seperate (module) function and do it step by step instead of doing it all in 1 go.

edit: Welcome to AWF

 

[Atomic Shrimp]

Let's say we have a string called strInput containing:
123456789#banana#987654

InStr(strInput , "#") returns 10, so we know the position of the first instance of #

InStrRev(strInput , "#") returns 17, so we know the position of the last instance of #

So InStrRev(strInput , "#")-InStr(strInput , "#") gives you (more or less) the length of the bit you want to extract

Mid(strinput, InStr(strinput, "#"), InStrRev(strinput, "#") - InStr(strinput, "#")) returns '#banana' - so we need to start from one position further on:

Mid(strinput, InStr(strinput, "#")+1, InStrRev(strinput, "#") - InStr(strinput, "#")) returns 'banana#' - so we need to make the extracted portion one character shorter:

Mid(strinput, InStr(strinput, "#")+1, InStrRev(strinput, "#") - InStr(strinput, "#")-1) seems to work...

However, if you pass it: 123456789#banana#0000#apple#987654, it will return 'banana#0000#apple' and if you pass it a string with less than two # characters in it, it falls over...

 

[gemma-the-husky]

another way is to use split (after A97)

split(string,"#") will give you an array of three strings

1) the part before the first #
2) the bit inbetween
3) the bit after the second #

so you can easily just get the middle bit directly from the array.

not sure offhand of the exact syntax, as I am not in front of an A2003 machine

 

[DCrake]

To follow on from Mikes post it may be better to create a function in a standard module for this exercise

Public Function ExtractText(AnyText As String, Symbol As String) As String

Dim FirstInst As Integer
Dim LastInst As Integer
Dim strStringLen As Integer

'First check for the existance of the symbol

If InStr(AnyText,Symbol) = 0 Then
   ExtractText = AnyText
   Exit Function
End If

'Next check to see if there is only one occurance of the symbol

FirstInst = InStr(AnyText,Symbol)
LastInst = InStrRev(AnyText,Symbol)
If FirstInst = LastInst Then
   ExtractText = Mid(AnyText,FirstInst+1)
   Exit Function
End If
'Calculate the length of the string between the first instance and last instance of the symbol

strStringLen = ((LastInst-1) - (FirstInst+1))

ExtractText = Mid(AnyText,FirstInst+1,strStringLen)

End Function

To Test

Str = ExtractText("abc#123#def","#") ' returns 123

Like Mike said though multiple instances of the symbol only show the outer string.

Str = ExtractText("abc#123#def#345#xyz","#") ' returns 123#def#345

If have included the symbol (#) in the function should the symbol ever need to be changed.

David

David

 

[Booker-T]

I am trying to pull Text from a table that is storing the data in XML format. The code you guys explain here looks like it might give me some direction but I'm not sure how to use the code you explained here? I don't use Modules or Macros in MS Access. I tried creating the Module but when I run it, it pops up with a message box asking for a Macro Name? I usually just use queries or VBA to perform record set lookups. What I want basically need to do is loop through a long data set that is stored in a table in XML format that you see down below and extract only the words seen between <object_data> and </object_data>. So looking below, the text that I want is "they kept diverting me". Since all the data in the table has the same XML format, Is there a way to programmatically extract the text that is located between <object_data>they kept diverting me</object_data>?
Any help or suggestions is greatly appreciated and thank you in advance!

<?xml version="1.0" encoding="ISO-8859-1"?>
<message type="imessage">
<header>
<from>
<from_username>Bubba</from_username>
<from_name>Bubba Smit</from_name>
</from>
<to_list>
<to>
<username>Bubbu</username>
</to>
</to_list>
<thread>796b7064090516203127</thread>
</header>
<data>
<object>
<object_type>text</object_type>
<object_format>txt</object_format>
<object_data>they kept diverting me</object_data>
</object>
</data>
</messa

 

[jdraw]

If you have the xml in a filesystem file, this sort of approach could work.
There is no error handler nor check to ensure your "nodes" actually exist.
You have to identify the folder and file names you are using.
And you have to reference the Microsoft Scripting Runtime

Sub GetTextInXml()
      Dim ofso As FileSystemObject
      Dim tsin As TextStream, RequiredText As String
      Const ForReading = 1, ForWriting = 2, ForAppending = 3
10    Set ofso = CreateObject("Scripting.FileSystemObject")
20     Set tsin = ofso.OpenTextFile("C:\Users\Mellon\Documents\testxml.xml", ForReading)
      Dim x As String
      
30    x = tsin.ReadAll
40    RequiredText = Mid(x, (InStr(x, "<object_data>") + 13), InStr(x, "</object_data>") - (InStr(x, "<object_data>") + 13))
50    Debug.Print RequiredText
End Sub

Tested Output:

they kept diverting me

 

[Booker-T]

I would need to do it by pulling it in a record set because the data reside in a live table that I am connecting to via an ODBC connection. They XML is not in a txt file. The table name is [IMSG_Conversation] and the Memo field that contains the XML data is called [data]. See attached image of the table. Thanks for you help!

 

[jdraw]

OK.
Here's a skeleton.

'---------------------------------------------------------------------------------------
' Procedure : GetXMLFromTable
' Author    : mellon
' Date      : 02-Dec-2016
'---------------------------------------------------------------------------------------
'
Sub GetXMLFromTable()

10  On Error GoTo GetXMLFromTable_Error

20  Dim RequiredText As String, x As String

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
30  Set db = CurrentDb
40  Set rs = db.OpenRecordset("IMSG_Conversation")

50  Do While Not rs.EOF
60      x = rs!data              'this is the memo field in the table
 
    ' *check if the xml node is present --if not give error 8000

70      If InStr(x, "<object_data>") = 0 Then
80          err.number = 8000
90          GoTo GetXMLFromTable_Error
100     End If

110     RequiredText = Mid(x, (InStr(x, "<object_data>") + 13), InStr(x, "</object_data>") - (InStr(x, "<object_data>") + 13))
120     Debug.Print RequiredText
130     rs.MoveNext
140 Loop

150 On Error GoTo 0
160 Exit Sub

GetXMLFromTable_Error:
170 If err.number = 8000 Then
180     MsgBox   " <object_data> not found in the memo field "
190 Else
200     MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure GetXMLFromTable of Module AWF_Related"
210 End If
End Sub

Test data attached in jpg