0

When I use code from [Remove Characters][1] it works correctly bar Ascii45 which is a dash -. Excel tells me the Ascii code is 45 (=Code(cellref)) however I find the data goes from say 2016-01-31 to 31/01/2016 but the column is not formatted. Does I have to reformat all the columns after or is Excel showing the wrong Ascii.

Full Code

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String
    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 33, 35 To 38, 40 To 41, 43, 45 To 47, 48 To 57, 65 To 90, 92, 97 To 122, 123 To 126, 32: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function
Sub CleanAll()
    Dim rng As Range
    For Each rng In Sheets("uploadsheet").Range("A1:AY5").Cells 'adjust sheetname and range accordingly
        rng.Value = AlphaNumericOnly(rng.Value)
    Next

    'http://www.asciitable.com/
    'http://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp?lq=1
End Sub
  [1]: http://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp?lq=1

I have currently added the below code after it's run but I'd like to make it work in one...

uploadsheet.Range("A2", "A50000").NumberFormat = "yyyy-mm-dd"
Community
  • 1
  • 1
indofraiser
  • 1,014
  • 3
  • 18
  • 50

1 Answers1

1

Replace:

rng.Value = AlphaNumericOnly(rng.Value)

with:

rng.Value = "'" & AlphaNumericOnly(rng.Value)
Gary's Student
  • 95,722
  • 10
  • 59
  • 99