# excel中以指定分隔符从右往左截取字符&多重判断

1. REPT(" ",LEN(A1))的意思是得到一空格串，长度为A1的长度。
REPT函数可可以按照定义的次数重复现实文本，相当于复制文本。

2. SUBSTITUTE(A1,"/",REPT(" ",LEN(A1)))
将A1中的字符"/"用多个空格串(第1步所得)来替换。此时，可获得的文本如下：

 http:                                                                            www.163.com                                      sports                                      Arsenal.html

SUBSTITUTE(text,old_text,new_text,instance_num) 函数是在文本字符串中用 new_text 替代 old_text。

3. 在以指定空格串代替所有分隔符后，就可以使用Right函数，从右往左取子串了。即
=RIGHT(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),LEN(A2))
此时得到的字串如:

 Arsenal.html

4. 最后使用Trim函数将空格删除，可得到结果:Arsenal.html
=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",LEN(A2))),LEN(A2)))

1 经典法：if多重判断

2 vlookup函数妙用法

=VLOOKUP(E2,B2:D5,3,TRUE)

3 VBA进阶1：select case函数

Function profitrank(income) As String

Select Case income

Case Is <= 0

profitrank = "<=0"

Case Is <= 5000

profitrank = "0-5千万"

Case Is <= 10000

profitrank = "5千万-1亿"

Case Is <= 20000

profitrank = "1亿-2亿"

Case Is <= 30000

profitrank = "2亿-3亿"

Case Is <= 40000

profitrank = "3亿-4亿"

Case Is <= 50000

profitrank = "4亿-5亿"

Case Is <= 100000

profitrank = "5亿-10亿"

Case Else

profitrank = ">10亿"

End Select

End Function

4 VBA进阶2：switch函数

Switch(条件1,”结论1”,条件2,”结论2”,条件3,”结论3”,…..)

=Switch2(A1<1000,"D",A1<5000,"C",A1<10000,"B",TRUE,"A")

Function Switch2(Test1 As String, Result1 As String, _
Optional Test2 As String, Optional Result2 As String, _
Optional Test3 As String, Optional Result3 As String, _
Optional Test4 As String, Optional Result4 As String, _
Optional Test5 As String, Optional Result5 As String, _
Optional Test6 As String, Optional Result6 As String, _
Optional Test7 As String, Optional Result7 As String, _
Optional Test8 As String, Optional Result8 As String, _
Optional Test9 As String, Optional Result9 As String, _
Optional Test10 As String, Optional Result10 As String, _
Optional Test11 As String, Optional Result11 As String, _
Optional Test12 As String, Optional Result12 As String, _
Optional Test13 As String, Optional Result13 As String, _
Optional Test14 As String, Optional Result14 As String)

Switch2 = Switch(Test1, Result1, _
Test2, Result2, _
Test3, Result3, _
Test4, Result4, _
Test5, Result5, _
Test6, Result6, _
Test7, Result7, _
Test8, Result8, _
Test9, Result9, _
Test10, Result10, _
Test11, Result11, _
Test12, Result12, _
Test13, Result13, _
Test14, Result14)
End Function

5 结论：if如果做多重判断，是不方便了，如果考虑到简单实用，建议用vlookup，如果还想多做VBA的学习，那还是用后面的两个VBA方法。

2、在使用公式或函数后，为何双击单元格右下角“十”字，没有作用？

如果因为特殊原因 不可以双击填充 则选择要填充的区域  按下ctrl+D 进行填充。

• 广告
• 抄袭
• 版权
• 政治
• 色情
• 无意义
• 其他

120