getting rid of characters between numbers

[AllenBeverly]

I am dealing with account numbers that need to be matched when I upload into our system.


The numbers are in text format and are received from external sources where you can't
count on the same format from month to month.

What I am really trying to do is change 12345_6789 or 12345-6789 to 123456789 so I can
have a consistent account number to match to when I upload.

A resolution to this would be fantastic!

Thanks!
AB

[pbaldy]

For one or two I'd use the Replace() function to replace the undesired character with nothing (""). If there are many, I might use a function that looped through the characters and only let the desired characters through.

[orange]

I agree with Paul if that's the range of potential "account numbers" you receive. A function and loop may be needed if you can get other punctuation, alphabetic or extended ascii chars.

[AllenBeverly]

How would that look in code?
One problem is that there is no consistent standard.
I want something that will account for both examples.

[pbaldy]

Here's my code template, adjust as desired.

Public Function ReplaceSpecial(varInput As Variant) As Variant
  Dim strString               As String
  Dim x                       As Integer
  For x = 1 To Len(varInput)
    Select Case Asc(Mid(varInput, x, 1))
      '32 = space, 47 = /, 48-57 = 0-9, 65-90 = A-Z, 97-122 = a-z
      Case 32, 47 To 57, 65 To 90, 97 To 122
        strString = strString & Mid(varInput, x, 1)
    End Select
  Next x
  ReplaceSpecial = strString
End Function

[AllenBeverly]

Thanks for your quick response.
I need to digest this for a while.

Even though I have been using Access for over 20yrs
now I think I need to explain how I use it. I am not aware
of anyone who uses it for what I do.

I am tied up right now at
work so I will respond sometime tomorrow.

Again I appreciate your quick response.

[pbaldy]

No problem, and welcome to the site by the way!

[AllenBeverly]

The way I use access right now is as a workbench for the data system that I am working on.
My current system is cloud based so I also use a tool that allows me to access the tables in
the system similar to if I was accessing from a sql server.

I export the necessary data into different access databases depending on the data
(transactions, customers, users, etc.) So using Access I am creating my own sql server.

This allows me to accomplish things that would not be possible any other way that I am
aware of which is not important to my current problem. I do not use access to set up for
other users anymore and am several years away from any coding I have done in the past.

I was not happy with the amount of changes MS made from xp to current versions so I have
only used it for working with data systems and find it a tool that is invaluable for what I do.

As for the function you wrote I understand it but am not sure how to use it for what I am
trying to do.

I would appreciate any additional help you could give to help me "see the light".

Thanks!
AB

[AllenBeverly]

I think I am going to handle this by adjusting the code in the data system
instead of the other way around.

Thanks for your help but I should have thought of this before.
But the good thing is it introduced me to this forum and that's a positive.

[pbaldy]

Sorry, late start due to snow. Glad you have a solution. To use the function you'd incorporate it into whatever method you use to import data (or to update it after import). Shouldn't be hard if you go back that way.