Juli 2009

Mouse Pointer Won't Move - Clean the Mouse!

Geeks Computer Tech - 03.17

If you have a mouse that has a ball underneath rather than a red light then you are going to have to clean it every now and then. A lot of computer users get confused when the mouse pointer starts to stick, move in jerks, or fails to move at all despite them running the mouse around the desk like a maniac. What has happened is that the rollers that the ball presses on to convert the movement into electrical signals have got covered in grease and dust and the ball is slipping on them instead of rotating them. <a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SmrckrEcK-I/AAAAAAAAE5g/HlILBRP5tLs/s1600-h/20090725a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5362340828932746210" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SmrckrEcK-I/AAAAAAAAE5g/HlILBRP5tLs/s400/20090725a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 268px;" /></a>Turn the mouse over and you will see a plate covering the ball which can be unscrewed by applying pressure with a finger to each side and turning anti-clockwise about a quarter turn. <a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SmrdOR5hEII/AAAAAAAAE5w/rE_14gyzgLc/s1600-h/20090725b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5362341543730548866" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SmrdOR5hEII/AAAAAAAAE5w/rE_14gyzgLc/s400/20090725b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 268px;" /></a>Turn the mouse back over and the plate and the rubber ball should drop into your hand. <a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SmrdYClKmJI/AAAAAAAAE54/qqW3SJhzQBQ/s1600-h/20090725c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5362341711417350290" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SmrdYClKmJI/AAAAAAAAE54/qqW3SJhzQBQ/s400/20090725c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 268px;" /></a>Inside the ball cavity are two long rollers that will be covered in a certain amount of gunge and grot! Look at the spot in between the two rollers and opposite that spot will be a small spring-loaded roller that keeps the ball pressed to the two larger rollers. <a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/Smrd1cqW_HI/AAAAAAAAE6A/qDDUrCR1Hsw/s1600-h/20090725d.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5362342216634662002" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/Smrd1cqW_HI/AAAAAAAAE6A/qDDUrCR1Hsw/s400/20090725d.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 268px;" /></a>Now it's a matter of scraping off the covering of grease and dust, rotating the rollers a little at a time until they are clean all the way round. You could be slightly amazed at the amount of greasy fluff you have to remove! It is caused by the mouse rolling over areas where you have previously rested your hand and the skin's natural moisture mixes with dust to form this material. Clean the small spring-loaded roller too - this is a bit more difficult because the pressure of your finger makes it retreat into the body of the mouse against its spring! <a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/SmregaWCAoI/AAAAAAAAE6I/PA2PP9kUZ78/s1600-h/20090725e.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5362342954746905218" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/SmregaWCAoI/AAAAAAAAE6I/PA2PP9kUZ78/s400/20090725e.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 268px;" /></a>Once you have cleaned the rollers, use a small paint brush to clean the ball cavity. The ball itself probably won't need cleaning but in extreme cases it can be cleaned by brushing it with a slightly stiffer brush. Don't be tempted to wash or wet it. Clean Blue Tack can be useful to pick off bits, but make sure you don't leave any shreds of Blue Tack on the ball. Before putting the ball back, clean off any debris and grease from the underside of the mouse casing - the feet and around the edges of the label. Lastly drop the ball back in, taking care not to handle it unnecessarily - you'll just transfer more grease onto it - and then place the retaining plate back over it, aligning the lugs and twisting clockwise to secure it. Your mouse should now work perfectly for another couple of weeks or so!

03.17

Excel: Manipulating text with LEFT, RIGHT, LEN, FIND, IFERROR

Geeks Computer Tech - 02.56

This entry solves the problem of having a single field for someone's name in a spreadsheet but then wanting to split it into forename and surname - perhaps because you want to sort rows alphabetically by name - hmm... yes... there's another entry to come! This one was suggested by a colleague, Patrick Bellis and he did actually provide a solution as well. I've had a fiddle and come up with an enhanced version though that shows how to deal with entries that have two forenames or a middle name or initial. It doesn't deal with more than one middle name or initial although you can do that easily - details later! <a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SmbkrfRzgDI/AAAAAAAAE4o/G3d1q5EEQNE/s1600-h/20090722a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5361223842212839474" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SmbkrfRzgDI/AAAAAAAAE4o/G3d1q5EEQNE/s400/20090722a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 228px;" /></a>So in my simple example, the Full Name field was the previously existing field and I have used formulae to split this into the two new fields, Forename and Surname. Now I have to say that this isn't my preferred way of doing this. My preferred way would be to use a Visual Basic (or VB) routine in a macro, but whilst these work for me quite easily in MS Access - the database application that comes with Microsoft Office Professional, I've never managed to get VB macros to work in Excel even when my efforts look exactly like the example in the Help pages. Someone else will have to suggest a fix for that perhaps? The VB routine would allow me to search for the last space within the name field. Using a simple formula, we have to search for the first space and that's why there's a problem with people who have middle names. To get over this partly - more specifically to deal with people who have a single middle name or initial - I've used a third new column which I've placed over to the right, out of the way where it doesn't need to be seen or printed. It's doing nothing other than an interim calculation or manipulation of the Full Name field. <a href="http://4.bp.blogspot.com/_i9xWjDR4D-E/Smblu2MAnRI/AAAAAAAAE4w/SeKFO6sZFQ4/s1600-h/20090722b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5361224999413783826" src="http://4.bp.blogspot.com/_i9xWjDR4D-E/Smblu2MAnRI/AAAAAAAAE4w/SeKFO6sZFQ4/s400/20090722b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 224px;" /></a>I've left a blank column between the working bits of the spreadsheet and this interim field but in reality you may want to hide it away far over to the right - say in column Z or even ZZ! This interim field searches for the first space and will contain all the text that comes after that first space. But before we look at the formula for that field let's deal with the simplest formula - that for the Forename field. Before we even do that let's look at the common syntax (the use of characters and the order they must be used) that formulas in Excel have. Any formula starts with an equals sign (=). This tells Excel that we want it to calculate something rather than treat the following characters as a string of text. Many formulas use brackets (or parentheses to give them their posh name) to contain any parameters the formula needs to work and use commas (,) to separate each parameter. The formula name tells Excel what to do and the parameters tell it what to do it to and how. A bit like: =DO-THIS(Do it to this, Do it like this) Formulas can be nested. That means we can use one formula to calculate the parameters for another formula. To find the Forename from the Full Name we need a formula that does this: RETURN THE CHARACTERS(from the Full Name, until you reach a space) For the Forename in Row 3 (so in the cell C3) the formula is: =IFERROR(LEFT(A3,FIND(" ",A3,1)),"") There are three different functions here and as with all mathematical formulae you should start by understanding the innermost set of brackets first. That set contains the parameters for the FIND keyword. The FIND formula counts the character that contains the first character of the string we want to find (the example will make this clear). The syntax for FIND is: FIND(the string of text you want to find, the string of text you want to find it within, the first character within the original string that you want to start searching from) Whoa! Too Much! Don't panic yet - I'm about to explain step by step. Step 1. The string of text we want to find is a space. So in the actual formula a few lines up you see ...FIND(" "... Step 2. The string of text we want to find the space within, is the Full Name field. So instead of actual text we have a pointer to cell A3. So far we have explained ...FIND(" ",A3... Step 3. I've put a figure one - 1 - because I want to start searching from the first character. Now; you could argue that there shouldn't be a space until at least the second character so I should have used a 2. But whilst there shouldn't that doesn't mean there won't be a space as the first character. Someone might have wanted to put their first initial and then their surname but not pressed the initial key hard enough. So by using a 1 instead of a 2, we do a simple bit of error trapping. So that should explain ...FIND(" ",A3,1)... The result of that bit of the formula is the number of the character that contains the space as counted from the left. So as cell A3 contains the text "John Burke" the FIND formula should find the space in character 5 and that is the "result" or "return" of the FIND formula. In the next cell down when considering the string "Seamus O'Leary", the space is the 7th character so the FIND formula returns 7. Thus as we consider the next most inner bit of the formula - which is the LEFT keyword - I can simplify the formula for you. Instead of considering: LEFT(A3,FIND(" ",A3,1)) we can consider it as: LEFT(A3,5) because Excel calculates the innermost formula first and works outwards, so once the FIND formula has run the LEFT formula works using the result. The LEFT formula chops and returns the text from the start (left) of the target string depending on how many characters you want. The syntax for LEFT is: LEFT(a string of text, the number of characters starting at the left of the string that we want to use) Again the string of text we want to use is contained in cell A3 so we have LEFT(A3... and the number of characters we want to use (as calculated by the FIND formula) is 5. So LEFT(A3,5) is the same as LEFT("John Burke",5) and the result is "John ". Note that there is a space at the end, because we asked for 5 characters. It won't show so it doesn't matter, but if you wanted to be pedantic (anyone having problems with the formula should ignore this as over complicated) we could have used LEFT(A3,FIND(" ",A3,1)-1) which would have subtracted 1 from the value returned by the FIND. Ahem! But that's just being a clever clogs! So that just leaves us with the IFERROR bit of the formula and what that does is it stops Excel from coming up with an error message if there is no space at all. Basically the syntax for IFERROR is: IFERROR(I want Excel to do this, but if it finds an error then do this instead) In the last parameter I've used two double quote marks "" - or in other words I've told Excel that if there is an error, instead of reporting that there's been an error it can show the text within the quote marks. Which is nothing. No text at all. The cell would be empty. Excel isn't good at telling you what's wrong; it just tells you there has been an error. <a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/Smb3qhqi_SI/AAAAAAAAE44/nqQ-Ewbv7u8/s1600-h/20090722c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5361244716394544418" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/Smb3qhqi_SI/AAAAAAAAE44/nqQ-Ewbv7u8/s400/20090722c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 217px;" /></a>Without the IFERROR formula if there is no space at all then you just get the dreaded #VALUE! error, which tells you absolutely nothing! See Row 7 in the screen grab. And so to the surname... Unfortunately there is no way of saying 'Now take the rest' after having done a LEFT formula. But there is a RIGHT formula which does the same as the LEFT only from the rear of the text string. RIGHT(a string of text, the number of characters starting at the right of the string that we want to use) Our problem now is that just because we know the position of the space, that still doesn't tell us how many characters we want from the right hand end of the Full Name, because we don't know how many characters there are in the surname. So we need to know how long the entire string of text is and to do that we use the LEN formula: LEN(a string of text) The LEN formula only needs a single parameter and returns a number which is the number of characters contained within the string. LEN("Fred Bloggs") would return 11. (it's counting the space as a character don't forget) LEN(A3) returns 10 - because cell A3 contains the text "John Burke" What we need for the Surname is the length of the Full Name minus the length of the Forename. So including our error trap use of IFERROR in case there is no space character, our formula is: =IFERROR(RIGHT(A3,LEN(A3)-FIND(" ",A3,1)),A3) Break it down from the innermost formula(s) in the nest. That's LEN and FIND. Both return numbers and we are taking LEN formula and then subtracting the result of the FIND formula. That gives us the number of characters to be returned by the RIGHT formula and as long as there is a space existing in cell A3 then we have our final result. The IFERROR formula this time, instead of returning no text at all, returns all of the Full Name field - assuming that any text with no spaces will be a surname. You can change this round if you keep a spreadsheet of celeb names such as Madonna or Prince! IFERROR(the main formula,A3) Now I just need to explain the interim field. The nested formula for the Surname as described above would take everything from the first space. So if there is a middle name or initial then you get that plus the Surname. I've created an interim field with the formula just described and then the Surname field has a formula that is exactly the same but which takes its original text string from the interim field instead of the Full Name field. You could have as many interim fields as you want, each whittling a middle name or initial out of the final surname, and with no errors even if there is no middle name. 12 such interim columns would let you deal with those unfortunate people whose father named them after the long-forgotten football team in the 1960s or 1970s!

02.56

Excel: Merge Cells and Multiple Lines in Cells

Geeks Computer Tech - 03.52

A couple of related formatting tricks that you can use within a spreadsheet this time. Let's start with a look at a standard bit of data. <a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMpovAdyvI/AAAAAAAAE1g/vQq2CAOq50Q/s1600-h/20090707a.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355670161663576818" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMpovAdyvI/AAAAAAAAE1g/vQq2CAOq50Q/s400/20090707a.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 231px;" /></a>Column A holds the collective heading - the people in Column B in the first four rows belong to Team A and then Team B personnel are held in rows 5 to 8. We could just repeat the descriptive header by selecting cell A1 and copying it down to the next three rows. But it then starts to become less clear because there is too much confusing text. So instead we'll merge cells A1 to A4 together as a single cell. In Excel a range of cells is written using the syntax A1:A4 - the colon means "through to". <a href="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMqnR32oCI/AAAAAAAAE1o/-45tQRRR_us/s1600-h/20090707b.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355671236174585890" src="http://1.bp.blogspot.com/_i9xWjDR4D-E/SlMqnR32oCI/AAAAAAAAE1o/-45tQRRR_us/s400/20090707b.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 255px;" /></a>So we need to select the four cells by clicking into A1 and then <a href="http://computerbasicsforfree.blogspot.com/2008/11/click-and-drag.html">click and dragging</a> until the range A1:A4 is selected. Then we select the Merge & Center whilst tutting slightly at the American spelling and hey presto, the four cells become one whilst at the same time the text has been centred in the horizontal plane. The text though has moved to the bottom of the new cell, to the spot previously occupied by cell A4. This is because Merge and Center (tut!) is more often used to merge cells next to each other horizontally - grouping a number of column headings for instance. We can do a couple of things now to format that and they include centring (darn it - the American spelling would look better there...) the text vertically and twizzing it round (Lancashire/technical term for aligning it vertically). <a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMtSR8cW8I/AAAAAAAAE1w/FHGYrVxFtls/s1600-h/20090707c.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355674173951466434" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMtSR8cW8I/AAAAAAAAE1w/FHGYrVxFtls/s400/20090707c.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 313px;" /></a>Select cell A1 (the merged cell still has that designation) and <a href="http://computerbasicsforfree.blogspot.com/2008/11/just-right-click.html">right-click</a> the mouse to bring up the dialogue box shown. Now select Format Cells. <a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMuPhrHqOI/AAAAAAAAE14/mOE_SsjrXqs/s1600-h/20090707d.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355675226145794274" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMuPhrHqOI/AAAAAAAAE14/mOE_SsjrXqs/s400/20090707d.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 287px;" /></a>The resulting dialogue window allows us to format lots of things but for now we are interested in the Alignment tab (pointy hand 1). We can alter the text alignment to centre the text both horizontally and vertically (pointy hand 2) and we can twiz (or is it twizz?) the text (pointy hand 3). Having clicked the Ok button and resized the cell by double-clicking the right cell wall to get the best fit the result is as shown below. <a href="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMxMEZR48I/AAAAAAAAE2I/BmlQJ8uAp3M/s1600-h/20090707e.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355678465281614786" src="http://3.bp.blogspot.com/_i9xWjDR4D-E/SlMxMEZR48I/AAAAAAAAE2I/BmlQJ8uAp3M/s400/20090707e.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 314px;" /></a>The pointy hand shows the simple way of aligning text left, right or centred in both horiziontal and vertical planes. The AB button with the little arrow to the right of the pointy hand can be used to twiz text - it opens the same dialogue box we have just used. That's one way of doing it. Here's the other - which may appear simpler, but may not meet your needs. It's always good to have an alternative! If whilst typing the names of the team members we used the ALT-Enter key combination we can force cell B1 to accept multiple lines within the single cell. So we type John Tharmy then hold down the ALT key and whilst it's held down, tap the Enter key. Now without moving from the cell type Sally Vaite and use ALT-Enter again until we read the last name. Now tap the Enter alone as you would normally do and the list of names each occupy a line within the single cell. <a href="http://2.bp.blogspot.com/_i9xWjDR4D-E/SlMw8Hv8ThI/AAAAAAAAE2A/6QICIljwGT4/s1600-h/20090707f.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5355678191304068626" src="http://2.bp.blogspot.com/_i9xWjDR4D-E/SlMw8Hv8ThI/AAAAAAAAE2A/6QICIljwGT4/s400/20090707f.jpg" style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 400px; height: 249px;" /></a>Job done!

03.52

Geeks Computer Tech

Mouse Pointer Won't Move - Clean the Mouse!

Excel: Manipulating text with LEFT, RIGHT, LEN, FIND, IFERROR

Excel: Merge Cells and Multiple Lines in Cells

Popular

Blog Labels

Blog Archive

Inspirasi

Inspiratif

Join This Site

Tips