Excel: Converting word cases for spreadsheets

Here's how you can change the case of text in an excel spreadsheet:

Microsoft Excel has the following three worksheet functions that you can use to change text case:

LOWER - Converts all uppercase letters in a text string to lowercase.

UPPER - Converts all lowercase letters in a text string to uppercase.

PROPER - Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
By using these functions, you can change the case of existing text in a worksheet. Consider the following example.

Type the following values in a worksheet:
   A1: ABCD
   A2: EFGH
   A3: IJKL
   A4: MNOP
   A5: QRST
   A6: UVWX
   A7: YZ
    
To make these cells lowercase in Excel 2007 or Excel 2010, follow these steps:  
  1. Type the following formula in cell B1:
    =LOWER(A1)
  2. In column B, select all the cells to the right of the cells that contain data in column A. In this example, highlight cells B1 through B7.
  3. Click Home, click Fill, and then click Down.

    Note The new column now contains the lowercase version of the text in column A.

    Note You can also fill cells B2 through B7 by clicking cell B1, and then dragging the fill handle to cell B7.
  4. Highlight cells B1 through B7, click Home, and then click Copy.
  5. Click cell A1, click Paste, click Paste Special, click Values, and then click OK to paste the converted values back onto column A.
  6. Delete column B. To do this, click Home, click column B, and then clickDelete.
The text that was in column A is now in lowercase:
   A1: abcd
   A2: efgh
   A3: ijkl
   A4: mnop
   A5: qrst
   A6: uvwx
   A7: yz
    


To make these cells lowercase in Excel 2003 and earlier versions of excel, follow these steps:
  1. Insert a column to the right of column A by selecting column B and clicking Columns on the Insert menu.
  2. Type the following formula in cell B1:
    =LOWER(A1)
  3. In column B, select all the cells to the right of the cells that contain data in column A.
  4. On the Edit menu, point to Fill, and then click Down.

    The new column now contains the lowercase version of the text in column A.
  5. With the same range selected, click Copy on the Edit menu.
  6. Select cell A1, and click Paste Special on the Edit menu. Under Paste, click Values, and click OK to paste the converted values back on top of column A.
  7. Delete column B by selecting the column and clicking Delete on the Edit menu.
The text that was in column A is now in lowercase:
   A1: abcd
   A2: efgh
   A3: ijkl
   A4: mnop
   A5: qrst
   A6: uvwx
   A7: yz
    


Revised from Microsoft's article "How to change the case of text in Excel":
http://support.microsoft.com/kb/263580

Excel Tip - Remove all numbers except area code


This equation takes a phone number with area code and strips the 7 numbers after the area code, leaving only the area code:

=LEFT(TEXT(E1,"0"),LEN(TEXT(E1,"0"))-7)

You can change the variables to clip and prune whatever info off either side of a number to get what you need.



Recover Deleted Sticky Note

"To restore your Sticky Notes backup, first you must delete all of the existing Sticky Notes on your desktop. Then, replace the current StickyNotes.snt file with your backup file by copying and pasting it in the %AppData%\Microsoft\Sticky Notes\ folder."

Source & more details here: http://carlcheo.com/how-to-recover-accidentally-deleted-sticky-notes

Camtasia - .camrec file to .avi conversion

Turns out that .camrec files created by Camtasia are actually zip files that contain the .avi that was recorded. You can use a program like 7-Zip to extract the .avi file

And if you want to save some space, you can always compress that .avi file with WinRar. It looks like the .camrec zip file is not compressed - however I've been able to take a 5GB+ avi file from Camtasia and compress it down to under 400MB.

Exchange 2007 - Unified Messaging Server Role SBS2008

From my archive notes: 10/25/2012

While trying to integrate an Avaya IP Office into Exchange 2007 I found that there was no "Unified Messaging Server Role" service installed. So I went to the control panel to change the install for Exchange Server 2007 to enable that Role. Two address paths were defined when adding this service.

First for the Exchange Server installation files:
C:\Program Files\Windows Small Business Server\Bin\CMPNENTS\EXCHSRV80

Second for the Exchange Server installation path:
C:\Program Files\Microsoft\Exchange Server

Our server did not have any folder located in the default installation files path and I didn't have my Exchange server install CD handy. Looking at several guides online, users have complained that using the original files from their install CD caused issues. So I went online and found the latest service pack that I had installed and decided to extract the files from there. Turns out I had Exchange Server 2007 Service Pack 1 (x64) installed.

I was able to download the "Exchange Server 2007 Service Pack 2" files from here:
http://www.microsoft.com/en-us/download/details.aspx?id=4479

The exe file was about 884 MB. After extracting the file using WinRAR, the file size was about 1.22 GB (now I can see why the files were not on my server). At one point or another, a previous tech had cleared out these files to save space on the server. From my workstation where I extracted the files, I opened the shortcut to my server and copied the files to the C:\Program Files\Windows Small Business Server\Bin\CMPNENTS\ under a new folder named "EXCHSRV80"

I decided on waiting a couple of days for the weekend as I didn't want to take the server down for updates in the middle of the workweek...

Notes and Resources:
http://social.technet.microsoft.com/Forums/en-US/exchangesvrclients/thread/64a9409a-e57a-4431-932e-b1426a17f66e


Deep research on this issue:There are two options (if you need to use the SBS DVD 2). Pick one.
·  Copy the CMPNENTS\Exchange12 directory from DVD2  to C:\Exchange 12
·   Add a registry key :
MediaPackage  REG_SZ \CMPNENTS\Exchange12\
under:  HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\461C2B4266EDEF444B864AD6D9E5B613\SourceList\Media
More information for your reference:
PRB: Error 1706 "No Valid Source Could Be Found" When Installing MSI File from Removable Media
http://support.microsoft.com/default.aspx/kb/299803

Avaya CallStatus EAccessviolation error


Found this guide to stop the EAccessviolation error associated with Windows 2003 server when running Avaya's CallStatus program. A user named cascadia on the http://www.pbxtech.info Forum had this to say and it worked for me:

====================

There is a very easy fix to this. I was advised to update the server fully with MS patches. Did that, but no help. So I did the following on a hunch and it worked!

My Computer > Properties
Advanced Tab
Performance > Settings
Data Execution Prevention Tab
This is turned on by default with a blank exception list.
Click Add...
Locate your copy of callstatus.exe
Mine was in C:\Program Files\Avaya\IP Office\CallStatus\callstatus.exe
Click Apply > OK > OK

And now you should be able to execute the Call Status monitor and run it like you used to.

Source: http://www.pbxtech.info/showthread.php/3114-EAccessviolation-error

Old Notes - Upgrade from EXC2007 SP1-SP3

Souce & Resources:
http://msmvps.com/blogs/bradley/archive/2011/03/11/exchange-server-2007-sp3-does-not-update-the-exchange-server-active-directory-schema.aspx

EXCHANGE SERVER 2007 SP3 DOES NOT UPDATE THE EXCHANGE SERVER ACTIVE DIRECTORY SCHEMA:

Exchange Server 2007 SP3 does not update the Exchange Server Active Directory schema: http://support.microsoft.com/kb/2457729/en-us?sd=rss&spid=10926
After you install Microsoft Exchange Server 2007 Service Pack 3 (SP3) on a computer that has Exchange Server 2007 Service Pack 2 (SP2) installed, the Exchange Server Active Directory schema is not updated.

This issue occurs because the Active Directory schema version information in Exchange Server 2007 SP3 is the same as in Exchange Server 2007 SP2.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Let's translate....
If you installed Exchange 2007 sp2 on pretty much any box with Exchange including SBS 2008 you will find that after you apply SP3 you can't add any new receive connectors.
To work around this issue, run the Setup.com /prepareschema command.
Let's translate again:  To now fix this issue since the install was supposed to upgrade the schema automatically, download SP3 again (or if you still have it on the box find it again) and follow the info in this blog post -- http://blog.korteksolutions.com/cant-create-a-new-receive-connector-after-installing-rollup-1-or-2-for-exchange-2007-sp3

To avoid this issue, run the Setup.com /prepareschema command before you install Exchange Server 2007 SP3.
Let's translate one more time.  If you jump from Exchange 2007 sp1 to sp3 you'll avoid this issue altogether.  It's only those of us who kept our servers reasonably up to date that got nailed on this.  This only impacts those who installed SP2 and then SP3.  If you've already installed SP3 and you were on SP2 you can't avoid the issue since you didn't know it existed in the first place.  Nor may you realize that you can't build a new receive connector until you go try to build one. 
So if you are still on sp2 and haven't gone to sp3 (like me) as you were waiting to see if there would be an automatic fix to this issue, there won't be.  Just be sure to run that prepare schema command before you apply sp3 and all will be well.  If you already did apply sp3 and can't do a receive connector, don't worry, merely manually running the command to upgrade the schema will do the trick.
Filed under: 

# re: Exchange Server 2007 SP3 does not update the Exchange Server Active Directory schema:

Monday, March 28, 2011 12:23 PM by Andy Helsby
In my case I wasn't able to install a new connector until I removed the rollup and then created them. Updating the schema did not make any difference. It was thanks to Kyle's blog that I got my server working though.
As Kyle doesn't allow comments to this older post, my comment was on the next available post - blog.korteksolutions.com/after-converting-a-sbs-2008-server-from-physical-to-virtual-with-backup-exec-system-recovery-sysprep-hangs

http://blog.korteksolutions.com/cant-create-a-new-receive-connector-after-installing-rollup-1-or-2-for-exchange-2007-sp3/


Can’t create a new Receive connector after installing Rollup #1 or #2 for Exchange 2007 SP3

After you install Exchange 2007 service pack 3, and or rollup #1 or rollup #2 and you attempt to create a new receive connector, you receive the following error:
Summary: 1 item(s). 0 succeeded, 1 failed.
Elapsed time: 00:00:00
test
Failed
Error:
Active Directory operation failed on SBS01.ks.local. This error is not retriable. Additional information: The parameter is incorrect.
Active directory response: 00000057: LdapErr: DSID-0C090C26, comment: Error in attribute conversion operation, data 0, v1772
The requested attribute does not exist.
Exchange Management Shell command attempted:
new-ReceiveConnector -Name ‘test’ -Usage ‘Custom’ -Bindings ’0.0.0.0:25′ -Fqdn ‘test.test.com’ -RemoteIPRanges ’0.0.0.0-255.255.255.255′ -Server ‘SBS01′
Elapsed Time: 00:00:00
image


This is caused by service pack #3 not extending the schema properly, or not at all. To resolve this issue, download service pack 3 again at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1687160b-634a-43cb-a65a-f355cff0afa6&displaylang=en and then extract it.
Open a command prompt and go to the extracted folder. Now execute the following command:
Setup.com /PrepareSchema

image
Now attempt to create your receive connector, and it will work fine. Please note, you do NOT have to remove rollup #1 or rollup #2 to fix this issue. I found one post that mentioned waiting for rollup #2 to come out to fix the issue http://msmvps.com/blogs/bradley/archive/2010/10/12/can-t-do-a-new-receive-connector-after-exchange-2007-sp3-rollup-1.aspx , but the issue is not fixed in the rollup as it is a schema extension that was introduced in service pack #3. Upon further reading and investigation, on the Microsoft TechNet site, at http://technet.microsoft.com/en-us/library/ff607233(EXCHG.80).aspxit clearly states
“You must prepare Active Directory and each domain before you perform the first installation of Exchange 2007 SP3 in an organization. This process includes updating the Active Directory schema for Exchange 2007 SP3″
image
Guess no one read this part, including me!