开发者

Excel formula, find the first instance of OU= in string and remove any characters before it

开发者 https://www.devze.com 2023-01-07 20:23 出处:网络
I have 1000\'s of strings similar to \"CN=Joe Smith,OU=Students,DC=Domain,DC=Edu\" and I want to find the first instance of OU= and remove the characters before it leaving me with \"OU=Students,DC=Dom

I have 1000's of strings similar to "CN=Joe Smith,OU=Students,DC=Domain,DC=Edu" and I want to find the first instance of OU= and remove the characters before it leaving me with "OU=Students,DC=Domain,DC=Edu". How 开发者_如何学运维can I do this with an Excel formula?

Many thanks Jamie


Use this:

=RIGHT(A1,LEN(A1)-FIND("OU=",A1)+1)


I combined jevakallio's answer with How do I recognize “#VALUE!” in Excel spreadsheets? for cases when the key "OU=" doesn't exist in the cell.

Situations:

CN=Joe Smith,OU=Students,DC=Domain,DC=Edu
CN=Jane Doe,DC=Domain,DC=Edu

Right Value checking for missing key:

=IF(ISERROR(RIGHT(A2,LEN(A2)-FIND("OU=",A2)+1)),"OU= Not Found",RIGHT(A2,LEN(A2)-FIND("OU=",A2)+1))

Results:

OU=Students,DC=Domain,DC=Edu
OU= Not Found
0

精彩评论

暂无评论...
验证码 换一张
取 消