This is great when you have a report you want to email on a regular basis. It is VBA code but you don’t need to know how it works, just how to adapt it to your needs.

First, Open the file you want to send reports from. Copy the code below and then press Alt+F11. This brings up the VBA editor. Paste all the code into a new module. You can run the macro from the Tools menu or create a button to run it.
I have created an example for you to play with. Click on the title above to see all the code.

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007  Read more »

I have been working with telephone numbers recently. As phone numbers are unique to individuals they are useful IDs to match two lists. However some can be formatted as numbers, some as text. This makes it hard to compare numbers in different lists.

I have a phone number 01234 567890, in Excel it will be displayed as 01234 567890 with the space. However  if it was entered as 01234567890, Excel will display it as 1234567890, without the leading zero. If the data has been read in from a text fill in could be formatted as text and still show 01234567890.

When I get data in from an unknown source, I use two functions to help me get a number that is usable in any situation.

First I substitute any spaces =SUBSTITUTE(A3,” “,”"). This will remove any spaces at the beginning, middle or end.

Then I format the number to text =TEXT(B3,”00000000000″). This gives me 01234567890 in my cell. Note the 11 zeros in the text function. this gives me the leading zero for phone numbers.

I do of course do all this in one cell =TEXT(SUBSTITUTE(A3,” “,”"),”00000000000″). I can then use this result as the lookup to compare to other lists and match up phone numbers.

I was asked the other day “How do I change the text in an Excel spreadsheet so they’re all uppercase or all lowercase?” I guessed that text had already been entered so I gave this response.

Personally I don’t like upper case text it looks the writer is shouting. To convert cells that have upper case text to lower case use the =LOWER() function.

If you need to shout and use upper case then =UPPER() will convert text to upper case

If you are looking for titles try =PROPER(). This will convert to text that has the first letter of each word capitalised.

If I have the following text in a cell: Here is my Text

• UPPER() returns HERE IS MY TEXT
• LOWER() returns here is my text
• PROPER() returns Here Is My Text

I don’t believe that there is an option in Excel to enter data and have it formatted as Upper or Lower case.