Access Replace Strings Of Characters

[Hanna]

Hi, we are cleaning up some data.
And to help us to identify patterns, we would like something that could [NB we will keep the original data, but update these results to a different field!]
Change any letter a-z, A-Z to "L"
Change any digits 0-9 to "9"
Change any remaining characters such as dots, dashes, underscores (anything really!) to "_"

Could somebody please help?

Hope everybody is keeping safe out there!

Best
Ellison

 

[pbaldy]

This doesn't do exactly what you want but should show you a way to get there:

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

 

[Hanna]

Thanks, I'm getting some unexpected results I'm afraid.

Best
Ellison

 

[pbaldy]

Which are? Without seeing how you modified it or how you're using it, and how the results differ from what you expect, I'd only be guessing.

 

[Hanna]

Many thanks.......... I'll do some digging at this end and try to figure out where I'm going wrong.
All the best

 

[isladogs]

Crossposted with answers at https://www.utteraccess.com/forum/index.php?showtopic=2057625&hl=

@Hanna/Ellison
To save anyone wasting time assisting you when you have a solution, please follow cross posting etiquette.
See https://www.excelguru.ca/content.php?184

 

[pbaldy]

Many thanks.......... I'll do some digging at this end and try to figure out where I'm going wrong.
All the best

I'm happy to help, but have to post the modified code, how you used it, etc.