INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VAT numbers and string extraction

VAT numbers and string extraction

(OP)
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

RE: VAT numbers and string extraction

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

CODE -->

Function ExtractNumber(ByVal pStr As String) As Long
'Extract Number from String
'http://www.access-programmers.co.uk/forums/showthread.php?t=219652
'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 


? extractnumber("EBS-12345 6")
123456

RE: VAT numbers and string extraction

Quote:

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:
23123456E

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.

RE: VAT numbers and string extraction

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),' ' , ''),'-','')

RE: VAT numbers and string extraction

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

RE: VAT numbers and string extraction

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

CODE

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
            'do nothing
    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.

RE: VAT numbers and string extraction

(OP)
Thanks for the replies.

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

Dan

RE: VAT numbers and string extraction

Regular Expressions to the rescue ...

CODE

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 

RE: VAT numbers and string extraction

(OP)
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

RE: VAT numbers and string extraction

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.

RE: VAT numbers and string extraction

(OP)
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.

RE: VAT numbers and string extraction

What MajP says

RE: VAT numbers and string extraction

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

RE: VAT numbers and string extraction

(OP)
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.

RE: VAT numbers and string extraction

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close