自定义函数“从右往左find”，“取缩写”

自定义函数“从右往左find”，“取缩写”  

2010-08-21 10:25:09|  分类： Excel自动化 |  标签： |字号大中小 订阅

从右查找

Function Rfind(x As String, s As String) As Variant
Rfind = InStrRev(x, s)
End Function

注意，得到结果是从左边算起第几位，比如“abcdab”从右查找a，则得到的是6，也就是最后一个a的位置。
实际上，可以用数组公式：=MATCH(1,0/(MID(A1,ROW($1:$99),1)="a"))

如果要从右边数，得到2，可以用：

Function Rfind(x As String, s As String) As Variant
Rfind = Len(x) - InStrRev(x, s) + 1
End Function

取缩写

Function SX(x As String) As String
Dim s As String
Dim i As Integer
x = " " & Trim(x)
s = ""
For i = 1 To Len(x)
If Mid(x, i, 1) = " " Then
s = s & Mid(x, i + 1, 1)
End If
Next i
SX = s
End Function

加字符

Function Join(x As String, s As String)
Dim i As Integer
Dim y As String
For i = 1 To Len(x)
y = Mid(x, i, 1) & s
Join = Join & y
Next i
End Function