作者:乔山办公网日期:
返回目录:excel表格制作
B2=INDEX(D2:K2,MATCH(,0/(D2:IV2<>""),))----数组公式
C2=LOOKUP(9^9,D2:IV2)
公式下拉
Public Function getleftstr(str As Range)
olen = Len(str.Value)
Dim odic As Object
Set odic = CreateObject("Scripting.Dictionary")
Firt = Left(str.Value, 1)
num = 1
odic.Add Firt, Firt
For i = 2 To olen
t = Mid(str.Value, i, 1)
If Not odic.exists(t) Then
odic.Add t, t
num = num + 1
If num = 3 Then
Exit For
End If
End If
Next
items = odic.items
For ind = 0 To 2
st = st & items(ind)
Next
getstr = st
End Function
这个VBA代码的是从左往右取值,按你说的取3个不同值。
以下代码是从右往左取3个不同的值7a64e58685e5aeb9336
Public Function getstr(str As Range)
olen = Len(str.Value)
Dim odic As Object
Set odic = CreateObject("Scripting.Dictionary")
Firt = Right(str.Value, 1)
num = 1
odic.Add Firt, Firt
For i = olen - 1 To 1 Step -1
t = Mid(str.Value, i, 1)
If Not odic.exists(t) Then
odic.Add t, t
num = num + 1
If num = 3 Then
Exit For
End If
End If
Next
items = odic.items
For ind = 0 To 2
st = st & items(ind)
Next
getstr = st
End Function
不知道题主是不是想要这样的效果。函数公式如下:
=LEFT(A2,FIND("@",SUBSTITUTE(A2,"-","@",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))-1)
简单写了一下,这e799bee5baa6e997aee7ad94e59b9ee7ad94362是excel的宏
Sub a()
Range("a1").End(xlDown).Select
r1 = ActiveCell.Row
For j = 1 To r1
temp1 = Mid(Cells(j, "a"), 1, 1)
For I = 2 To 9999
If Mid(Cells(j, "a"), I, 1) <> temp1 Then
temp2 = Mid(Cells(j, "a"), I, 1)
count1 = I
Exit For
End If
Next I
For I = count1 To 9999
If Mid(Cells(j, "a"), I, 1) <> temp1 And Mid(Cells(j, "a"), I, 1) <> temp2 Then
temp3 = Mid(Cells(j, "a"), I, 1)
Exit For
End If
Next I
Cells(j, "c") = temp1 & temp2 & temp3
Next j
For j = 1 To r1
temp1 = Mid(Cells(j, "a"), Len(Cells(j, "a")), 1)
For I = 2 To 9999
If Mid(Cells(j, "a"), Len(Cells(j, "a")) - I + 1, 1) <> temp1 Then
temp2 = Mid(Cells(j, "a"), Len(Cells(j, "a")) - I + 1, 1)
count1 = I
Exit For
End If
Next I
For I = count1 + 1 To 9999
If Mid(Cells(j, "a"), Len(Cells(j, "a")) - I + 1, 1) <> temp1 And Mid(Cells(j, "a"), Len(Cells(j, "a")) - I + 1, 1) <> temp2 Then
temp3 = Mid(Cells(j, "a"), Len(Cells(j, "a")) - I + 1, 1)
Exit For
End If
Next I
Cells(j, "b") = temp1 & temp2 & temp3
Next j
End Sub