Menu
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
527
Press Ctrl + H to open the Find and Replace dialog. In the Find what box, enter one of the following combinations: To eliminate text before a given character, type the character preceded by an asterisk (*char). To remove text after a certain character, type the character followed by an asterisk (char*).
To delete the first character from a string, you can use either the REPLACE function or a combination of RIGHT and LEN functions. Here, we simply take 1 character from the first position and replace it with an empty string ("").
You use the FIND function to locate the first dash in the string and add 1 to that value because you want to start with the character that follows the dash: FIND("-",A2)+1. 3rd argument (num_chars). Specifies the number of characters you want to return.
Use this:
=RIGHT(A1,LEN(A1)-FIND("OU=",A1)+1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
