Question

excel如何取出单元格最右边括号内的字符串?

excel如何取出单元格最右边括号内的字符串?(单元格字符串长度不等,各个单元格有多个括号,取出最右边的括号内的字符串,不包括括号本身)

如:
A1单元格内容(第一次) *永远取出括号内的字符串:第一次*
A2单元格内容(第一次)(第二次) *永远取出括号内的字符串:第二次*
A3单元格内容(第一次)(第二次)(第三次)*永远取出括号内的字符串:第三次*

以此类推!!

不知哪位高人能给予指点!! 多谢了!!!

Answer 1

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+回车结束

Answer 2

假如你的数据在工作表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

Answer 3

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
下拉公式。

Answer 4

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
我针对你上面括号中有一个字或两个字，进行修改的。

Answer 5

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)