Excel - remove specific data from Column
I have a column that has listed all OUs of my company. I want to delete everything to the left of OU=Disabled. What excel function/code can i insert to make this happen? Ultimately i want it to look like the bottom 3.
distinguishedname
Manager\,Conference,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
questions,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
news,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
test\,devang,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
Steve test2,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
TP25,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
Barreca - Consultant\,Sal,OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=net
OU=Disabled,OU=Users,OU=Contoso_Users_and_Groups,DC=Contoso,DC=nettASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With the provided sample, a simple find and replace does what is required:
In Find box enter: *OU=Disabled
In Replace box enter: OU=Disabled
With the sample has the same result as doing the MID function suggested by Bill above.
In Find box enter: *OU=Disabled
In Replace box enter: OU=Disabled
With the sample has the same result as doing the MID function suggested by Bill above.
That's a valid point Rob, and certainly another way to approach it.
I did want to mention why I often choose a formula approach instead of in place replacement, just for the benefit of Isaias. But this is certainly personal preference, not a criticism of any proposed approach.
Depending on the situation I often prefer to leave the "source data" intact, and use a formula to extract or adjust it as I think I need in the next step. This way if I need something else later I can still go back to the original source data and process it differently. In some cases it's also useful or important to actually be able to cite the source data for the results that we process further.
There is no question that a replace in place approach can be the right one in certain cases, but I tend to approach it a bit more conservatively and create a new column of the massaged results. It also "documents" the changes / processing that was applied to the source data a little better (in my opinion) which sometimes I find useful, especially if there might be more than one adjustments I want to do to the source data.
Hope this helps, good luck, let us know if you need more help.
»bp
I did want to mention why I often choose a formula approach instead of in place replacement, just for the benefit of Isaias. But this is certainly personal preference, not a criticism of any proposed approach.
Depending on the situation I often prefer to leave the "source data" intact, and use a formula to extract or adjust it as I think I need in the next step. This way if I need something else later I can still go back to the original source data and process it differently. In some cases it's also useful or important to actually be able to cite the source data for the results that we process further.
There is no question that a replace in place approach can be the right one in certain cases, but I tend to approach it a bit more conservatively and create a new column of the massaged results. It also "documents" the changes / processing that was applied to the source data a little better (in my opinion) which sometimes I find useful, especially if there might be more than one adjustments I want to do to the source data.
Hope this helps, good luck, let us know if you need more help.
»bp
Totally agree about leaving source data intact, gives a full audit trail if required.
EE.xlsx