Hi, we have a brilliantly helpful UDF which removes any non-alphanumeric (0-9 or a-z or A-Z) characters out of any cell.
We were wondering if it would be possible to tweak this in order to leave alone any semi-colons? i.e. not trim them out / replace them
God knows how many non alpha numeric characters there are, but I’ve tried to include a few of them below in an example here…
Also, I should point out:
Also, we wouldn’t need to see the info in Clean-Code-Original, it’s just there to help throw some light on my ramblings
. It’s what this (brilliant!) UDF does:
UDF: Function CleanCode(Rng As Range)
Dim strTemp As String
Dim n As Long
For n = 1 To Len(Rng)
Select Case Asc(Mid(UCase(Rng), n, 1))
Case 48 To 57, 65 To 90
strTemp = strTemp & Mid(UCase(Rng), n, 1)
End Select
Next
CleanCode = strTemp
End Function
We were wondering if it would be possible to tweak this in order to leave alone any semi-colons? i.e. not trim them out / replace them
God knows how many non alpha numeric characters there are, but I’ve tried to include a few of them below in an example here…
Also, I should point out:
- the info in the Original-String is sometimes blank
- there can be multiple semi-colons in the info in the Original-String (or none)
| Original-String | CleanCodeV2 | CleanCode-Original | |
| ;ABC-12-434;XYX,002 | ;ABC12434;XYX002 | ABC12434XYX002 | |
| 3879-001;43/669; | 3879001;43669; | 387900143669 | |
| 61a4_2B C;test | 61A42BC;TEST | 61A42BCTEST |
Also, we wouldn’t need to see the info in Clean-Code-Original, it’s just there to help throw some light on my ramblings
. It’s what this (brilliant!) UDF does:UDF: Function CleanCode(Rng As Range)
Dim strTemp As String
Dim n As Long
For n = 1 To Len(Rng)
Select Case Asc(Mid(UCase(Rng), n, 1))
Case 48 To 57, 65 To 90
strTemp = strTemp & Mid(UCase(Rng), n, 1)
End Select
Next
CleanCode = strTemp
End Function
