excel cheatSheet

1. remove non alpha and replace space with '_'

VBA insert module:

 

Function CleanCode(Rng As Range)
Dim strTemp As String
Dim n As Long

trim_Rng = Trim(Rng)

For n = 1 To Len(Rng)
Select Case Asc(Mid(UCase(Rng), n, 1))
Case 48 To 57, 65 To 90
strTemp = strTemp & Mid(LCase(Rng), n, 1)
Case Is = 32
strTemp = strTemp & "_"
End Select
Next
CleanCode = strTemp
End Function

 

 

 

2. search

vlookup <--> match + index

 

转载于:https://www.cnblogs.com/fuxiaotong/p/7510771.html