How to extract number from memo field

[helen liu]

Hello,
I am Helen. Is some one can help me with this question.

for example how can I extract the following number from the field ITEM
117145 5410076462162, 714774 117072 5410076462223 ,714768 117153 5410076462285,733289 998214 5410076462193 from this record. The field is memo.
The common: the string "+" always is in front
I tried this
Expr2: InStr([ITEM],"+"), but it only give me first one.

The following is record
BARCODE Retail Line Code NSL Code Prod Ean Name Size Facings Capacity Pos Comments Offer Description Offer Type Offer No 5ELKAHQ*agiaji+ 714766 117145 5410076462162 NS PRNGLS SUR CRM AND ON 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiagh+ 714774 117072 5410076462223 PRNGLS ORIG 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agiddd+ 714768 117153 5410076462285 PRNGLS SLT/VNGR 190G 3F 15 BUY ONE GET ONE FREE MV 105574 5ELKAHQ*agibia+ 733289 998214 5410076462193 PRNGLS BBQ 190G 3F 15 BUY ONE GET ONE FREE MV 105574

Regards,

Helen

 

[pr2-eugin]

Hello helen liu, Welcome to AWF

You might be in need of a function to get what you want here. This might involve a bit of looping, and trimming/manipulating the input string so that all numbers are obtained.

 

[namliam]

In my database I have fields: Number1, Number2, Number3, Number4, Number5
which I fill from a memo field. For this I use this function... which loops thru all the characters and will skip anything that (for example) looks like a date or time.
It will also allow some predefined characters to be input between the numbers but continue extracting a "true" number.

I cut out some stuff here and there for privacy reasons.... but the general idea should still work. Should also be fairly easy if you want to to change this into a function...

Sub GetNumbersFrom(strTemp As String, _
                   countOfNumberFields As Integer, _
                   rsImport As DAO.Recordset)
    Dim i As Integer, n As Integer 'Integers for counting
    Const strAllowChars As String = " .,[-]\/" ' Allowed characters inbetween numbers
    Dim lenNumber As Integer       'The minimum acceptable length for a number
    Dim fullLenNumber As Integer   'The full length of a number eg 4000000600033103 (thats 16)
    ' Find the first empty Number# field
    n = 1 ' Field Counter
    Do While Len(rsImport.Fields("Number" & n) & vbNullString) > 0 And n <= countOfNumberFields
        n = n + 1
    Loop
    lenNumber = 8
    fullLenNumber = 16
    i = 1 ' character counter
    Do While Mid(strTemp, i, 1) <> "" And n <= countOfNumberFields
        
        ' Skip any numbers that are i.e. Dates or times
        If Mid(strTemp, i, 10) Like "##-##-####" Then ' 4 digit dates
            i = i + 10
        ElseIf Mid(strTemp, i, 8) Like "##-##-##" Then ' 2 digit dates
            i = i + 8
        ElseIf Mid(strTemp, i, 5) Like "##:##" Then ' Time Stamp
            i = i + 5
        ElseIf Mid(strTemp, i, 12) Like "##.##.##.###" Then
                                        '15.22.87.949 is a bankaccount number
            i = i + 12
        End If
        
        If Mid(strTemp, i, 1) Like "#" Then
            'If we hit a number without the looks of a date (12-12-04 or 12-12-2004), start building a number
            rsImport.Fields("Number" & n) = Mid(strTemp, i, 1)
            i = i + 1 ' next character
            '  skip any and all strAllowChars (agian inside while - wend loop)
            Do While InStr(1, strAllowChars, Mid(strTemp, i, 1)) > 0 _
                    And Len(Mid(strTemp, i, 1)) = 1
                    i = i + 1 ' next character
            Loop
            While Mid(strTemp, i, 1) Like "#"
                rsImport.Fields("Number" & n) = rsImport.Fields("Number" & n) & Mid(strTemp, i, 1)
                i = i + 1 ' next character
                '  skip any and all of strAllowChars
                Do While InStr(1, strAllowChars, Mid(strTemp, i, 1)) > 0 _
                        And Len(Mid(strTemp, i, 1)) = 1
                    ' if the number becomes to long, its of no use....
                    If Len(rsImport.Fields("Number" & n)) >= lenNumber Then Exit Do
                    i = i + 1 ' next character
                Loop
            Wend
            i = i - 1
            If Len(rsImport.Fields("Number" & n)) = 9 _
              And Left(rsImport.Fields("Number" & n), 1) = 8 Then ' Accountnumber
                rsImport!AccountNR = rsImport.Fields("Number" & n)
                rsImport.Fields("Number" & n) = "" ' Delete the contents which is now in AccountNumber
                n = n - 1 ' also reset the field number
            ElseIf Len(rsImport.Fields("Number" & n)) <= 4 Then
                ' Number is to short, forget about it
                rsImport.Fields("Number" & n) = ""
                n = n - 1
            End If
            n = n + 1 ' Next Number field
        End If
        i = i + 1 ' next character
    Loop
End Sub

I hope you can get the idea and adjust it to suite your needs.

 

[DavidAtWork]

you can use InStr, but you need to use it in conjuction with a loop as pr2 suggests above. Is the number part always the same number of numbers or can that vary, from your example below, the number part seems fixed at 27 characters, so you could try

Function extractNumberPart(incomingStr As String) As String
Dim myCheck As Boolean
Dim currentPos As Integer, nextPos As Integer
Dim outStr As String
myCheck = True
outStr = ""
currentPos = 1
nextPos = 1
Do While myCheck = True
    If InStr(currentPos, incomingStr, "+") > 0 Then
        nextPos = InStr(currentPos, incomingStr, "+") + 1
        If outStr = "" Then
            outStr = Mid(incomingStr, nextPos + 1, 27)
            Else
                outStr = outStr & ", " & Mid(incomingStr, nextPos + 1, 27)
        End If
        currentPos = nextPos
        Else
            myCheck = False
    End If
Loop
extractNumberPart = outStr
End Function

This function can be called from a query or another part in your code and the value returned should be as you require.
Create a query, add your table and copy this in a field line
myNumber1: extractNumberPart([ITEM])
and run the query

 

[pr2-eugin]

Many roads lead to Rome !

Public Function getNumbers(inputStr As String) As String
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim tmpStr As String, outStr As String, plusPos As Long
    tmpStr = inputStr
    
    [COLOR=Green]'Loop until the + sign is no where to be found.[/COLOR]
    Do While InStr(tmpStr, "+") <> 0
    
        [COLOR=Green]'Identify the Plus location in the String.[/COLOR]
        plusPos = InStr(tmpStr, "+")
        
       [COLOR=Green] 'Now get the Numbers (27 is used here)[/COLOR]
        outStr = outStr & ", " & Mid(tmpStr, plusPos + 2, 27) 
        
        [COLOR=Green]'Then get only the rest of the String after the numbers[/COLOR]
        tmpStr = Mid(tmpStr, plusPos + 27)
    Loop
    

    If Len(outStr) <> 0 Then
            [COLOR=Green]'We take away the last hanging comma[/COLOR]
            getNumbers = Trim(Mid(outStr, 2))
    Else
        getNumbers = vbNullString
    End If
End Function

 

[helen liu]

Hello All,

Thanks for help. I am really appropriated.

I will have a go. I am not good at VBA. If I can't figure out I will ask for help again.

Regards,

Helen

 

[helen liu]

Hello,
I have tried DavidAtwork's function. It works perfectly.

Many Thanks

Helen