excel如何取出单元格最右边括号内的字符串?
发布网友
发布时间：2022-04-22 00:02
共5个回答
热心网友
时间：2023-10-18 17:47
b1输入
=MID(A1,MAX(IF(MID(A1,ROW($1:$100),1)="(",ROW($1:$100),0))+1,MAX(IF(MID(A1,ROW($1:$100),1)=")",ROW($1:$100),0))-MAX(IF(MID(A1,ROW($1:$100),1)="(",ROW($1:$100),0))-1)
输入完按shift+ctrl+回车结束
两边出现大括号才对
然后向下拖
如果括号内的文字长度固定为3可以简化
=MID(A1,MAX(IF(MID(A1,ROW($1:$100),1)="(",ROW(1:100),0))+1,3)
输入完按shift+ctrl+回车结束
热心网友
时间：2023-10-18 17:47
假如你的数据在工作表sheet1的A列。
1、摁下组合键ALT + F11，进入VBA编辑器。
2、双击左上角工程资源管理器里面的sheet1，在右侧的代码区粘贴代码如下
Sub 获取左后一个括号内数据()
For i = 1 To [A65536].End(xlUp).Row
str1 = Cells(i, 1)
str1 = RegExpTest("\(.*\)", str1)
If Len(str1) > 1 Then
s2 = "(" & Split(str1, "(")(UBound(Split(str1, "(")))
End If
Cells(i, 2) = s2
s2 = 0
Next
End Sub
Function RegExpTest(patrn, strng)
Dim regEx, Match, Matches ' Create variable.
Set regEx = CreateObject("VBSCRIPT.REGEXP") ' Create a regular expression.
regEx.Pattern = patrn ' Set pattern.
regEx.IgnoreCase = True ' Set case insensitivity.
regEx.Global = True ' Set global applicability.
Set Matches = regEx.Execute(strng) ' Execute search.
For Each Match In Matches ' Iterate Matches collection.
retstr = retstr & Match.Value
Next
RegExpTest = retstr
End Function
3、光标定位到“ Sub 获取左后一个括号内数据()”后面。摁下F5运行程序。
OK
热心网友
时间：2023-10-18 17:48
b1输入：
=MID(A1,MAX(IF(MID($A1,ROW($A$1:$A$256),1)="(",ROW($A$1:$A$256),0))+1,MAX(IF(MID($A1,ROW($A$1:$A$256),1)=")",ROW($A$1:$A$256),0))-MAX(IF(MID($A1,ROW($A$1:$A$256),1)="(",ROW($A$1:$A$256),0))-1)
同时按 ctrl+shift+enter
下拉公式。
热心网友
时间：2023-10-18 17:48
sub
test()
dim
usecount
as
integer
with
activesheet
for
n
=
1
to
.usedrange.cells.count
for
m
=
1
to
len(.usedrange.cells(n))
usecount
=
m
if
mid(.usedrange.cells(n),
m,
1)
=
"["
and
mid(.usedrange.cells(n),
m
+
2,
1)
=
"]"
then
.usedrange.cells(n).characters(start:=m,
length:=3).font.color
=
vbgreen:
usecount
=
m
+
2
if
mid(.usedrange.cells(n),
m,
1)
=
"["
and
mid(.usedrange.cells(n),
m
+
3,
1)
=
"]"
then
.usedrange.cells(n).characters(start:=m,
length:=3).font.color
=
vbgreen:
usecount
=
m
+
3
m
=
usecount
next
m
next
n
end
with
end
sub
我针对你上面括号中有一个字或两个字，进行修改的。
热心网友
时间：2023-10-18 17:49
B1==MID(A1,FIND("@",SUBSTITUTE(A1,"(","@",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1,FIND("@@",SUBSTITUTE(A1,")","@@",LEN(A1)-LEN(SUBSTITUTE(A1,")",""))))-FIND("@",SUBSTITUTE(A1,"(","@",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))-1)
