I’m terrible with Excel. I know that using formulas will make my life easier, but I struggle. Terribly. Searching for information on the interwebs can also be difficult because everyone assumes you’re not an idiot. Anyway, for my own later reference, here’s a formula that came in very handy for me.
I dumped a query to a CSV file that contained a computer name, as well as the AD OU information. After spending some time sorting the list and shrinking it from thousands of computers to hundreds, I found that I made a mistake. The column that contained the OU information also included the computer name at the beginning, followed by a comma and space. Something like this:
|COMPUTER_NAME||COMPUTER_NAME, OU=Computers, DC=DOMAIN, DC=com|
The computer name in the second column was superfluous, so I wanted to remove it from the beginning of the second column. Of course, I wanted to do this automatically since the change needed to be made for hundreds of computers; however, this was somewhat complicated since the number of characters in the computer name varied.
Here’s how I ultimately solved the problem:
This calculates the number of characters in the first column (the computer name) and crops it from the beginning of the second column. Additionally, the “-2” takes off another couple of characters to account for the comma and the space. The result looks like this:
|COMPUTER_NAME||OU=Computers, DC=DOMAIN, DC=com|
Once everything seems good, just drag the cell down to apply the formula to the rest of the rows.
Now, go ahead and laugh about how much trouble I have putting together simple formulas. I’ll be crying in the corner.