Archive for the ‘MS Office Stuff’ Category
Microsoft “moved my cheese” when they came out with 2007, and then, darn, they moved it again in 2010. I’ve used Microsoft products since they began and am so used to things the way they used to be that sometimes I spend quite a bit of time trying to figure out where things got moved. It is really bad to have to admit I actually like the ribbons at the top now that I realize they keep the majority of things you use right in front of your face. However, they do a amazingly good job at hiding some things.
Because I work with HTML quite a bit, sometimes I want to bring in HTML code into a document. Then when I go to spell check it the dictionary goes all wonky and spastic trying to show me all the “supposed” misspellings. So, I said, self, why don’t you create an HTML style and tell it to not spell check. I found out how to not spell check during another seek and destroy mission.
Since this was a royal pain, I thought I’d put it into a blog article to save some other poor sole the effort of figuring this out. This will show you how to even begin to build a style – which is the first thing really hidden, and the second part will show you how to exclude spell checking on a style.
Ok, first things first. Where the heck did they hide creating a new style? Open Word and make sure you are on the Home Ribbon. Next click on the little corner widget on the Styles Group. I have learned that these little corner widgets hide a myriad of secrets, and usually have what I am looking for in the first place.
After clicking on the corner arrow, you will see the following drop-down. The icon you are searching for, and which is totally non-obvious is shown here on this graphic. The green arrow is pointing at your destination.
Hooray, we can now build a new style. As I said I built one for HTML Code. The next mission was to set the style to do no spelling checking. Let me explain where you can use this. Say you are writing a blog article about HTML coding – or even more probable, a user manual. You can use a normal default style and when you are entering the HTML code, go to the Style Group and select your new HTML Code style. Then, when you run your spell checker it will bypass spelling checking that section. In my case, it is HTML code. It could be technical terminology for engineers, or Visual Basic or C# code for programmers, or very broad medical descriptions for doctors. I think you get the idea.
So now that I have you interested, let me show you how to set the style to not spell check. In Word, again make sure you are on the Home ribbon and click on the Styles corner arrow. Locate your style and right click on the name to bring up the dialog box. You are going to choose Modify as shown here in this graphic.
After clicking Modify you want to click the Format button.
On the format dialog box choose the Language selection.
Finally, you can now check the Do Not Check Spelling or Grammar box.
Whew, see, I told you it was non-intuitive. I know it didn’t used to be this hard. The good news is all the clicking and searching causes me to find some things that are indeed really new. But I don’t know about you, I really don’t have the time to have to go through this every time I need to do something out of the ordinary. Yes, I know, I can go look it up on Help, but the Microsoft Help, as my husband likes to say, “makes up for lack of function by being slow.” That combined with my total lack of patience spells disaster or a search and destroy mission – ergo, the title of my blog article.
That’s it for today. Several of my readers have sent me emails asking for more regular updates. They like the ones I do on Microsoft products, so look for more in the near future. Who knows – maybe it will become a series of articles.
Yesterday, I got an interesting request for an Excel formula. This person wanted to take the first word in a string, and add it to the end of the string, preceded by a comma. In other words, The cat in the hat would become cat in the hat, The.
Time to put on the thinking cap. I figured this would be a concatenation formula. I would get the first word, assume that a space would be the delimiter to break apart the first word, search for the remainder of the cell and concatenate that with the first word along with a comma. There were couple of ways I could do this. I’ll explain a couple of ways here along with what I finally sent him.
First off, a while ago, I found on the web some code that created a function in Excel called GETFIRSTWORD. The function looked like this:
Function GETFIRSTWORD(Text As String, Optional Separator As Variant)
Dim firstword As String
If IsMissing(Separator) Then
Separator = ” ”
firstword = Left(Text, InStr(1, Text, Separator, vbTextCompare))
GETFIRSTWORD = Replace(firstword, Separator, “”)
In Excel 2007 or 2010, to add a user defined function (often called a UDF) you open the Developer Tab and click on Visual Basic. In earlier versions, you click on Tools – Macros. Once in VB you want to insert a MODULE. Give the module some name that makes sense. I always call mine “Myfunctions.” You can have more than one function in a module. Paste the code shown above into the Module. To use the function, you simply enter a the following in a cell: =GETFIRSTWORD(xx) where XX is the cell number.
The function assumes that a space separates the words but allows for delimiters as well. For example, say I had a string of words separated by semi-colons. The formula to enter into a cell would be: =GETFIRSTWORD(A1,”;”). Cool beans.
The next thing I needed to do was get the remaining part of the cell string. That was a simple search formula shown here: =RIGHT(A1,LEN(A1)-FIND(” “,A1)).
Using my function, the concatenation formula to build this would be:
=concatenate(RIGHT(A1,LEN(A1)-FIND(” “,A1)),”, “,GETFIRSTWORD(A1)).
Broken down, the concatenate is joining the remaining part of the string with a comma and the first word. Works like a charm. But then, I said to myself, could this be totally done in a formula without a function. And the answer was yes. Here’s what I came up with.
Basically I needed a formula to get the first word, and without the cool function, needed to put in what was the character or location to search and then pull all characters to the left of that character. In this case, it was a space. Therefore, I needed to find the first occurrence of a space in the cell. In the last sentence, the magic word is FIND. Ah ha, there is a FIND function in Excel. Using the FIND function, I had this:
This said go look in A1, find a space, and show me everything to the left. Presto. I have my first word. Now, it was just a matter of merging together the two functions along with a concatenate command. This is what I created:
=concatenate(RIGHT(A1,LEN(A1)-FIND(” “,A1)),”, “,LEFT(A1,FIND(” “,A1)-1))
The concatenate command joined everything to the right of the first word, a comma, and then everything to the left of the first space. In the example above where you might have a string of data separated by semi-colons, you would replace the space with a semi-colon and get the same results.
Intriguing problem that was fun to figure out. I figured it would make a cool blog article.
Footnote: Here’s the link where I originally found the GETFIRSTWORD function. http://www.teachexcel.com/free-excel-macros/m-138,udf-get-first-word-cell-excel-free-macro.html
In the process of helping clients, I often stumble on cool new things or tips. It is nearly impossible to learn everything about an application or device. I find it’s when I’m helping others that I learn the most.
So, this week, here’s some goodies I came across that I will share with all. They are in no particular order – just how I am finding them in my brain’s backup repository, dark dusty place that it is.
First tip. My contact manager of choice, ACT, works with Outlook. Ok, cool. So, in Outlook, ACT becomes another address book. Now, imagine you have a Global Access List (often called a GAL) if you are on an Exchange server, plus any local address books and now an ACT address book to boot. How do you tell Outlook which one to search first. Ah, that’s the tip here. This tip may be different depending on your version of Outlook, but I think the core stuff all is the same. Click on Tools – Address Book. Easy enough. Now, in that dialog box click on the Tools menu within the Address book. Don’t think I’ve ever clicked on that menu item. That’s the secret (or not so secret) place where you can choose which list to show first and in what order. Nifty cool indeed.
Second tip. Ever send something to the printer and have it print backwards – like looking at a document in a mirror? Well, it turns out that there is a setting called Mirror Image on a lot of printers and it can get set somehow by other print jobs. When you go to print the document having issues, click on Properties or Options or whatever points you at the Landscape/Portrait section. The Mirror Image is there. Who knew…
Third tip. If you read this blog, you know we work with the ACT contact manager program. A cool ACT is that in version 2009, you can type an email address in Notes or Histories and it becomes “hot” meaning you click on it and it opens an email box. This even works on the detail section of an Opportunity. That one rates a star.
Last but not least the forth tip is another ACT tip that can be used in other applications as well. First off, let’s set the stage. HTTP://, FTP://, Mailto://and FILE:// are URI’s (Uniform Resource Identifier). We all should know what they do. One opens a web page, one connects to an FTP site, one sends an email and one opens a file on your computer. With me so far? Well, another URI is CALLTO://. This is the nifty one. It will start up whatever program is set on your computer for voice calls over the Internet – say like Skype. You can find what your computer is set to use on Internet Explorer in the Tools – Internet options section under programs. Look for Internet Calling and see what shows up. If you have Skype you can either choose it or Netmeeting which comes with Windows. I believe if you have something like Vonage, it shows up as well, but don’t hold me to that. Now, what’s the ACT tip. In ACT, you can create a field and set it to be a URL field. That means when you type in an address, it turns it click-able (also meaning hyperlink) and you can then initiate a voice call from that field. Taking that a bit farther – in the field you would type callto://+12223334444 or whatever the number is. I think you get the idea. If you have another application of any sort, say like a Word document, that allows you to put in hyperlinks, you can put in a link that will trigger a voice call. Think of the ways you can utilize that neat idea. Just start a paragraph saying something along the lines of “Do you have Skype? Want to call us today? Click here.” Presto. Instant marketing. This one rates two stars. And a smiley – 8-).
That’s all folks. Have a good weekend.
Working with Excel is one of my favorite things to do. It is fun to cruise the Net using Google to find the great websites that are specific to Excel and Office products. Along the way I am accumulating a library of links and articles and several people know that I’m on this mission. Mark Mondo, a good friend, sent me a link to an excellent MSDN blog about Excel. The link he sent is an article about data cleanup using Excel. Wow, what a great find. I’m including the link here so others can find this as well. Thanks Mark.