自定义函数VBA
输出相同函数:
按alt+f11 右键新建一个模块
查找相同
Function 查找相同(rg1 As Range, rg2 As Range)
For i = 1 To Len(rg1.Value)
If InStr(rg2, Mid(rg1.Value, i, 1)) > 0 Then
a = a & Mid(rg1, i, 1)
End If
Next
查找相同 = a
End Function
判断是否一致:
=IF(OR(ISERROR(FIND(A1,B1,1)),ISERROR(FIND(B1,A1,1))),"一样","不一样")
=IF(OR(ISNUMBER(FIND(A1,B1,1)),ISNUMBER(FIND(B1,A1,1))),"一样","不一样")
FIND(A1,B1,1)左边是否包含右边
FIND(B1,A1,1)右边是否左边
ISNUMBER(FIND(A1,B1,1)
提取第一个汉字及其之后的所有内容:
1.数组公式:=MID(A2,MATCH(1=1,LENB(MID(A2,ROW($1:$25),1))=2,),25)
使用组合键Ctrl+Shrift+Enter
2.VBA函数:
Sub CommandButton1_Click()
Dim arr, brr(), str$, mr%
mr =Range("a65536").End(xlUp).Row
arr = Range("a2:a" &mr)
ReDim brr(1 To UBound(arr))
For i = 1 To UBound(arr)
For j = 1 To Len(arr(i, 1))
str = Mid(arr(i, 1), j,1)
If VBA.Asc(str) < 0Then
brr(i) = Mid(arr(i, 1), j,Len(arr(i, 1)))
Exit For
End If
Next j
Next i
Range("e2:e" &mr).ClearContents
Range("e2").Resize(UBound(brr), 1) =Application.Transpose(brr)
End Sub