如何在excel中提取单元格中某字段、和替换

发布网友发布时间：2022-04-30 04:53

共1个回答

热心网友时间：2023-10-14 07:16

第一个问题用公式基本可以解决：
B1输入：=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
C1输入：=MID(SUBSTITUTE(A1,"(","oo",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))),FIND("oo",SUBSTITUTE(A1,"(","oo",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+2,FIND("pp",SUBSTITUTE(A1,")","pp",LEN(A1)-LEN(SUBSTITUTE(A1,")",""))))-FIND("oo",SUBSTITUTE(A1,"(","oo",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))-1)

如果还有很多，往下拉复制公式即可。
但是第二个问题用公式就不好弄了。只好用VBA，代码如下：

Private Sub CommandButton1_Click()
Dim i As Integer, j As Integer, k As Integer
Dim a As String, l As String, m As String, n As String
Range("A1").Select
While ActiveCell.Value <> ""
a = ActiveCell.Value
n = ""
For i = 1 To Len(a)
l = Mid(a, i, 1)
If l = "(" Then
l = "，"
Else
For j = 41 To 89
m = Chr(j)
If l = m Then
l = ""
End If
Next j
End If
n = n & l
Next i
ActiveCell.Offset(0, 3).Value = n
ActiveCell.Offset(1, 0).Select
Wend
End Sub

如果还有问题，QQ：657439757。