We help IT Professionals succeed at work.

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=nett

Open in new window

Comment
Watch Question

Commented:
Something like the find and mid function (see example in column B & A) might work.  Unfortunately I did not have good data to work with.  Copied your data to three lines.
EE.xlsx
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
You should be able to use a basic formula like below.  Place this in B2, and copy down (assumes data in column A, adjust as needed).

=MID(A2,SEARCH("OU=Disabled",A2),999)


»bp
Rob HensonFinance Analyst

Commented:
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.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
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
Rob HensonFinance Analyst

Commented:
Totally agree about leaving source data intact, gives a full audit trail if required.