VAT numbers and string extraction 1

[DanielUK]

Hi,

Using Access 2000 I can find queries/modules to remove certain characters/alphanumerics etc from strings but I am looking for a combination and I am not smart enough to work it out!

I have a list of alphanumeric VAT numbers from various countries which all start with a multi-lettered prefix of varying lengths, of which I need to extract and tidy up the remaining part of the string which may also contain letters and have spaces and dashes in that need removing. So for example:

GB212345B67
EBS23123456E
EBS-123456
EBS 12345 6

I need to remove the first alphas, a maximum of three and then with the remainder, remove any dashes or spacing so the above becomes:

212345B67
23123456E
123456
123456

Is that easy to achieve?

Thanks

Dan

 

[sxschech]

This might get you part of the way...function will extract the numbers and exclude spaces and other chars.

Function ExtractNumber(ByVal pStr As String) As Long
'Extract Number from String
'[URL unfurl="true"]http://www.access-programmers.co.uk/forums/showthread.php?t=219652[/URL]
'20160324
Dim intLen  As Integer
Dim n       As Integer
    pStr = Trim(pStr) 'removes leading & trending spaces
    intLen = Len(pStr) 'stores original length
    n = 1 'consider this a counter & position marker
    If pStr = "" Or IsNull(pStr) Or intLen = 0 Then Exit Function 'validate we didn't get passed an empty/null string
    Do
        If IsNumeric(Mid(pStr, n, 1)) Then 'check if that single character is a number
            ExtractNumber = ExtractNumber & Mid(pStr, n, 1) 'if it is add to existing ones if any
            n = n + 1 'add to counter so we know to go to next character on the next pass/loop
        Else
            n = n + 1 'it wasn't a number, add to counter so we know to skip it
        End If
    Loop Until intLen = (n - 1) 'go until we processed all characters. The reason we have to do n-1 is that Len starts at 0 & we told n to start at 1
End Function 'if no numbers function will return default value of data type, in our case long would be 0

[tt]
? extractnumber("EBS-12345 6")
123456
[/tt]

 

[Andrzejek]

remaining part of the string which may also contain letters and have spaces and dashes in that need removing

Do you want to remove the letter(s) from the remaining part or not?

Your example:[tt]
23123456[highlight #FCE94F]E[/highlight][/tt]
suggests: leave the letter(s)

sxschech's solution will not work since it returns Long (just number, no letters)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PWise]

try
should be
IIf(Asc(Left(a, 1)) >= 65 And Asc(Left(a, 1)) <= 122, "", Left(a, 1))
& iif(asc(mid(vat,2,1)) >= 65 and asc(mid(vat,2,1))<= 122 ,"",mid(vat,2,1) )
& iif(asc(mid(vat,3,1)) >= 65 and asc(mid(vat,3,1))<= 122 ,"",mid(vat,3,1) ))
& replace(replace(mid(vat,4,99),' ' , ''),'-','')

 

[sxschech]

Sorry should have been more clear about the function only gets numbers, but did mention it would do part of the issue.

 

[Andrzejek]

If only numbers need to be extracted, and no letters, spaces, dashes, etc.

Function ExtractNumber(ByVal strIn As String) As Long
Dim strOut As String
Dim i As Integer

For i = 1 To Len(strIn)
    Select Case Mid(strIn, i, 1)
        Case 0 To 9
            strOut = strOut & Mid(strIn, i, 1)
        Case Else[green]
            'do nothing[/green]
    End Select
Next i

ExtractNumber = CLng(strOut)

End Function

Not the most 'elegant' code, but it works.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[DanielUK]

Thanks for the replies.

Yes, I would need to keep the end letters...unfortunately! That's why it became beyond my skillset, lol.

Dan

 

[strongm]

Regular Expressions to the rescue ...

[blue]Private Function StripVAT(strSrc As String) As String
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "(^[A-Z]{1,3}|[ -])"
        StripVAT = .Replace(strSrc, "")
    End With
End Function[/blue]

 

[DanielUK]

Thanks, I was rather hopeful but......I have that as a module in my database, saved and compiled but when I try to run it in a query I get the rather unhelpful 'Undefined function 'StripVAT' in expression'. Now other modules work e.g. basic string extraction ones so I am wondering if the code is too new for my version of Access?

Thanks

Dan

 

[MajP]

No, that message means it cannot find a function called StripVat. The function needs to be Public and in a standard module. Since you probably copied it, it is Private.

 

[DanielUK]

Thanks. I did that, it was the first change I made, but made no difference after debug and save. So whether Private or Public I get the same error message.

 

[strongm]

What MajP says

 

[MajP]

Is it in a standard module and not a form, report, or custom class module?

 

[DanielUK]

It works now. I had called the module the same as the function...ooops. Thanks for the help and to strogm for providing the module.

 

[MajP]

Is the spelling of "StripVat" in the query correct? Make sure no typos.