Crop Cell Contents in Excel

If you are an avid user of excel or use Excel extensively at your work or school, then its good to know the formula built into excel to make your task easier.

For example, if you had a big list of "something" and you need to make a list of it of the first 5 characters, or maybe last 10 characters. Hmmm.

Don't think more, excel has an inbuilt crop function to do that. Use "LEFT" function to retain characters starting from left side of the text, and use "RIGHT" function to retain characters starting from the right end of the text. This below figure should help you understand better.

Its pretty easy right. Now go crop your text data. 

Okay, so what if you wanted to retain all the text from left but the last 3 characters? Don't worry, here is the formula for that. 

Hope this helps!

Same width for all Blogger images

If you are a blogger, you'd definitely take efforts for you blog to make look nicer. I've been looking for various ways for formatting my images on my blog and honestly until now I've been doing them manually for each image. So every time I change the width of the blog or change the template, the images all go may-hem and looks ugly.

On one of my recent "hunts" for formatting blog images, I found this code. And what this does is format all the images to a fixed width. The height automatically adjusts, so yay! 

Print file list from Command Window

Let's say you need a list of all the files in a folder. What would you do? A few files, may be you would note them down yourself manually. But what if you had 100 files, or even 1000?

So here's the tip. This also lists the files in an alphabetical order. Cherry on top?!

1. See my other post on how to open a command window in the folder. 
2. Now type the this without the quotes "dir /b /on >print.txt". Mind the forward slash and spaces. 
3. Now go to your folder, you will see a text file names "print.txt". 
4. Open it and it has a list of all the files in the folder, arranged neatly alphabetically. 

Hope this helps you make your list of files next time.

Open Command Window in Destination Directory

Hapy New Year!

It's been a while I've blogged about anything, been very busy lately. But now with the new year just starting, it's now a resolution to blog more often, let's see how well I can keep that one up.

Anyways, this is a very simple post but with a useful tip, if you use Command window very often.

Most common way is to type "cmd" in the Start menu, and hit Enter. For the older versions of Windows, you had to click on "Run", and then do the same.

Now if you had to navigate to a folder in the command window, you had to know where the folder is, then remember the path, then you have to type the lengthy path after the "cd" command. You know the drill. Painful, huh?

So here's the tip.

1. Navigate to the (parent) folder in Windows Explorer where you want to open the Command window.
2. Now Shift+Right-click on the folder where you want to be (in the command window). 
3. You will see the option "Open Command Window here".
4. Click on the option, and voilà you have your command window already in the directory you want to be. 

Isn't this so much easier and efficient? 

How to Revoke Permissions from Google Account given to Other Websites

Most people I know have a Google account. The one thing which I personally like about Google account, is that you can sign in to many popular websites without having to register and create a new account. Over some time, like me, most people might end up signing in with Google to so many websites that they forget they had given permission to those websites. With today’s growing concern about sharing information online, one needs to regularly check and keep track of such permissions. So this post is about where you can find the permissions given and how to revoke them.

Salary Calculators

Have you been in that awkward position when you are talking to your potential future employer on phone and they ask “How much do you expect”? You say $x/hour and then they say, ok we’ll give you $xxx/year. Ok!!! Now unless you are really good in math you don’t know how much that is going to translate to a monthly salary or how much that might convert to an hourly pay. Instead of scrambling for a calculator , here is a good calculator which makes your task easier.

AHK Tutorial #6 – Convert AHK Script to Standalone EXE Program

You have installed AHK and created a few simple scripts now. You have also run the scripts and seen them work. You would now have understood to run the AHK scripts you must have the AHK software installed. Correct. But what about those laptops where AHK is not installed? and you still want to run the scripts. Don’t worry, AHK has a compiler which will compile your scripts into a Standalone EXE Program which can be run on any laptop or computer without installing AHK.

Dynamic Views not showing up on Blogger

Okay, Dynamic Views has been here for a while now. Some people like it, some people hate it, and there are others who want to try it for a while. And if you are one of those who want to try it, but don’t see the option for Dynamic Views on your Template Settings on you blog, it means that there is are two settings that you have to turn on.

1. Your Blog has to be Public. Anybody must be able to read it without signing in.
   
   
2. Your Blog Feeds has to be set to Full. To set your blog feeds to be full, here’s what you have to do.
• Go to Settings > Other
• Select “Full” for Allow Blog Feed.
• Now go back to your Templates, and you should be able to see your Dynamic Views.
  

If this doesn’t work, let me know, we’ll try to get it to work.

Stop Kindle Fire from auto-changing wallpapers

Okay you’ve rooted your Kindle, added tons of new stuff. But you end up bowing to the default wallpapers of the KF every time you wake up your Kindle. Not good. You want to retain your wallpaper you set last time and don’t want your KF to change it when the screen goes off. Its easy to retain your settings.

You will need:

• Rooted Kindle Fire. Learn how to root our Kindle Fire here.
• Root Explorer App or any other explorer app like ES File Explorer or Astro File Manager etc. Any explorer which will let you mount your folder as a read/write will work.
• If you have rooted using Kindle Fire Utility, then you would also probably have the Go Launcher EX now. Its not really necessary, but it will be good to have this so you can install Live Wallpapers. Yay!

This is what we are going to do.

1. We are going to set up a wallpaper (not live wallpaper) and this wallpaper will stick on the apps background. After the change to the folder, this wallpaper cannot be changed. If you want to change it, you need to undo the permissions change we do, then change the wallpaper, and revert the folder permissions. But good news follows; You can set up live wallpaper for your KF Home and KF Lock screen. You can do this using the Go Launcher EX.
2. Now here is what you actually need to do.
3. Using Root Explorer navigate to this folder.

   data/data/com.android.settings/files

   
   
   
   
4. Press and hold the wallpaper item.
   
5. Select Permissions on the pop-up.
   
6. Uncheck the boxes for Write.
   
   
7. Done.

For easier access, you can bookmark this folder or create a favorite for shortcut access.

Now set up your wallpaper for Home and Lock Screen, and they wont change until you change them. Have fun!

Disclaimer: I’m not responsible for anything that happens to any device after following my posts. They work for me, and that’s what I’m sharing.

Books not showing up after rooting Kindle Fire

You have rooted your Kindle Fire and installed the Google Play Store App. You are downloading a lot of apps and games. Now you are done. You want to get back to reading.

Wait! Where are my BOOKS!!!?

So this post is about the problems you might encounter with reading Books after you root your Kindle Fire. Rooting has its benefits and drawbacks, but they are not too serious, hopefully this post should help you.

---

Problem: When you click on Kindle app, the screen freezes.

This is not the way to open your books after rooting. You need to use the Launcher from the Kindle to access your books. You original Kindle Carousel Home opens. Click on Books and there you can read your books. To get out of the freeze screen, click on the settings icons and then click on anything. You can now go back to the Home.

---

Problem: Books are not there. There’re gone!!!

Don’t worry, no matter what, your purchased Kindle books are always going to stay on your Amazon account. But to make them show up correctly, you need to do a few things.

1. Go to Google Play Store.
2. Click on Menu >> My Apps
3. Look for Kindle.
   
4. Click on Uninstall.
5. UN-check Allow automatic updating.
6. Go back to your Launcher and check your books.
7. Make sure you don’t update app.If you find you books missing again, you know what is wrong.

---

Problem: Still books not showing up?

Check you Amazon account.

1. Login to your Amazon account.
2. Manage your Kindle >> Manage your devices.
3. If you see unrecognized devices under your Android type, then De-Register them.
4. Go back to the previous problem, and follow the same steps.
5. Go to your Kindle account in Amazon, and deliver your books to your Kindle.
6. You should probably have your books visible now.

---

Hopefully this should help you get your books back. If not let me know, we’ll figure out what’s gone wrong.

AutoCorrect Automatically Every Time You Type

Nobody’s perfect. Everyone’s got to admit it. We all make typos. That’s why we have Autocorrect. On MS Word, its easy. I’ve also written a post how to Autocorrect when you type your emails in Gmail.  But we are not always using applications which have Autocorrect built in them or where we can add them.

So what do we do? Learn to type correctly. Just kidding, that’s surely an option, but there’s an easier way. Auto Hot Key. Yes, you gotta love that tool. Now how in the world are we going to achieve that? Using an AutoCorrect AHK script. Don’t worry you don’t need to code anything. Everything’s ready to go.

You’ll find links to download the files later. But first the steps.

1. You need to have AHK installed on your PC. This is how you can do it, you will also find the link to download AHK.
2. You may also want to read How to create an AHK script and How to make an AHK script run every time Windows starts.
3. Now download the file at the bottom of the post. You will use only one of them. I will explain it later.
4. I suggest you keep a separate folder for all your AHK scripts. Extract them.
5. Double-click on Autocorrect.ahk. If the file opens in the Windows Notification Area, then all’s good. But, if you get an error message like the one below, then run the other file – Autocorrect for AHK-L.ahk.
   
6. Done. Now anywhere you make a typing mistake and if it’s in this file, then it will correct it for you. Yay!

But wait, what if the mistake isn’t there in the file? Don’t worry. When you realize you have made a mistake, select that word, and press Win+H on your keyboard. You will get a dialog box like the one below.

See the example entry and enter the correct spelling on the right side, and click OK. There you go, you just added another word to the file, and one less word to spell correctly.

So here is download link.

AutoCorrect AHK Scripts in ZIP file.

You can now make as many spelling mistakes as you want. Happy typos!

How to create an AHK Script

For the newbies to AHK (that is Auto Hot Key), this is how you save or create an AHK script. If you don’t know what AHK is, it is a tool to make create scripts and customize your PC to make it run at your will. It is mainly used for automating certain actions you do on a very regular basis. See my short post on it.

1. Open a notepad.
2. Type or copy your AHK code/script.
3. While saving use -  
   1. Use file name as any file name followed by extension ahk.
      E.g.: fileabc.ahk
   2. Save type as: All files
4. Click Save.

You can use AHK files by double clicking on it to make them run. It is easier to make certain AHK scripts auto-run at startup, so that you don’t need to open them everytime you reboot your system. Learn here on how to make files auto-run at startup.

Make Programs Auto-run on Windows Startup

There are several ways to do it, for example create an autoexe.bat (still remember that), or using other 3rd party tools to set up the programs. But this post is going to be about a very simple way, and very easily done. This works in Windows OS.

1. Go to Start.
2. Select All Programs.
3. Scroll to Startup.
4. Right-Click on Startup and select Open.
5. Startup will open as a window.
6. Now go to the exe or the file you always want to run at windows startup.
7. Right-Click and select Copy.
8. Come back to the Startup folder.
9. Right-click and Paste Shortcut.
10. Done.

Now isn’t it easy?

I often use this method to make my AHK scripts load on Windows Start-up.

Change your Blogger theme in the New Blogger Layout

Finding the option to change your Blogger theme in the older Blogger Layout was easy to find. But in their new design, some people struggle at first. A few of my friends have even panicked that they removed the option to upload any xml theme and customize it. Don’t worry. Its just a little hard to find (at first).

Go to the Templates option on the left navigation.

Now the the top header, at the extreme right corner you will find the button to “Backup/Restore”. Click on it.

Now you will get a dialog for uploading the xml them or for backing-up your current theme. Choose your action.

There you go, now you can upload your custom xml theme just as before.

Root Kindle Fire 6.3 using Kindle Fire Utility

Okay I’m going to say the usual disclaimer stuff first. This post is about what I did to root my Kindle Fire (KF) and the problems I faced and how I solved them. I’m not responsible for any damage that happens to your Kindle Fire or any other device either by following the below steps or by downloading anything suggested here.

I’ve been using KF unrooted for a while now, and then I got bored of the stock keyboard. That was the actual thing to make me root it. So my search to find the easiest way to root began. I’m pretty sure that many might disagree with me, but I thought Kindle Fire Utility (KFU) is the best and easiest way to root KF.

Kindle Fire Utility can be downloaded from the xda-developers website. At the time of writing Version 0.9.6 is the latest and that is the one I’m using.

KF = Kindle Fire
KFU = Kindle Fire Utility

Downloading, Installing and Running KFU:

1. You will need a fully charged KF, and the USB cable to connect your KF to your laptop.
2. Download KFU and extract it to your desktop or a convenient folder. This is how your files will look like after extraction.
   
3. According to the instructions, all the drivers need to be correctly installed for your KFU to run correctly and detect your KF.
4. If you don’t have your drivers installed correctly, then you are supposed to run install_drivers.bat file. You can connect your KF now or not. It wouldn’t matter.
5. Connect your KF.
6. Run the file run.bat
7. Your screen will look something like this.
   

Problem: ADB Status is Offline
Now this is will probably be the first problem you encounter. Check my post on ADB Status is Offline while rooting Kindle Fire on how to resolve it.

Now hopefully your would have gotten your screen to show ADB Status as Online now. Proceed.

Selecting the options to root Kindle Fire:

1. Select option 2 for Install Permanent Root with Superuser.
2. Let the tool do its job, just wait for it, and keep watching the screen to know when its done. Your KF might boot several times here.
3. After its done, your screen will look like this.
   
4. Yay, you have successfully rooted your Kindle Fire.
5. Now lets check if the Superuser has been installed correctly. We need this Superuser because we will need to modify the read/write permissions on folders to have complete control of KF.
6. Open your kindle and check if the Superuser is showing in the carousel on top.  You can also check it from Apps. It should be showing up.
   

Problem: Superuser is not installed.
We will need the Superuser. So check my post on how to get Superuser on your Kindle Fire.
Hope this will get you your superuser on the KF.

Installing Google Play Store:

If you have to follow the steps for installing Superuser using apk from my other post, then you would probably already have Google Play Store installed here.
You will not able to access Google Play Store from the Kindle Launcher. You need to use Go Launcher EX. Other than that you have your Play Store. All apps may not be compatible with KF, so happy exploring.

If you did not have your Google Apps installed, then

1. Connect your KF to the laptop.
2. Run run.bat.
3. Make sure ADB status is online.
4. Select option 6 for Extras.
5. Select option 1 for Install Google Apps / Go Launcher EX.
6. Complete the installation.
7. You will be able to access Google Apps thru Go Launcher EX.
    

Hope this helps you to root your Kindle Fire, And install Google Play Store.
Again, I’m not responsible if anything happens to your Kindle Fire or any other device. This is what I did and it worked for me.

Rooting Kindle Fire using KFU: ADB Status is Offline

The usual disclaimer stuff first. This post is about what I did to root my Kindle Fire (KF) and the problems I faced and how I solved them. I’m not responsible for any damage that happens to your Kindle Fire or any other device either by following the below steps or by downloading anything suggested here. 

So this post is more of a follow up of the other post of mine which says how I rooted my Kindle Fire. So you are using Kindle Fire Utility to root your KF and after your run the Run.bat your screen looks like this.

ADB Status is Offline.

Possible Causes:

1. Kindle Fire is not connected properly.
   => Check your connections, and run run.bat again.
2. Drivers are not installed properly.
   => Run install_drivers.bat again and run run.bat again.

Okay you did both and it still says DB Status is offline. Okay at this point you need to do a few more steps, but don’t worry its really very easy.

1. Connect your KF.
2. Open Device Manager in your laptop.
   
3. Look for Android Phone >> Android Composite ADB Interface.
4. Right Click.
5. Click Uninstall.
6. Complete the Uninstall.
7. Disconnect your KF from the laptop.
8. Wait for a minute or so, and connect your KF again.
9. Now when you open your Device Manager on your laptop again, your will see Android Phone >> Android Composite ADB Interface.
10. There might be a small yellow caution sign on the ABD Interface, this is good.
11. Right Click on this again and click Update Driver Software.
    
12. Select Browse my Computer for driver software.
13. Select the location for the Kindle Fire Utility\drivers\kindle folder.
14. Complete updating the driver.
15. Run run.bat again.
16. Your screen will look like this.
    
17. Your ADB Status will be Online.
18. Now your can continue to root your Kindle Fire. See my other post on how to continue.

Hope this works for you. If not, let me know and we can probably figure out why its not working.

How to remove “ads not by this site”

Once in a while it so happens that you are seeing a lot more ads on most websites you visit, and the ads are what can be defined as not “family-friendly”. If you see “ads not by this site” below the ads in small gray font, then this post can help you fix it. I’m not sure if I've to call it a virus or a spyware, but its just something you don’t want. This is also the same which show more ads on Facebook and it displays “ads not by Facebook”. I’ve also have seen it happen on Hulu and other sites. It’s pretty simple to fix this.

Find Duplicate Cells in Excel

I use Excel very often, and I just need pointers to get my work done faster. So here's one for you as well.

Imagine you have a list, or actually several lists, of similar data, and want to identify all the duplicate cells. Less then 10 items, is really easy, but what if you have 50 or 500? You might say good luck with that. But here's a really simple easy way.

Assume this is your set of lists.  

Select all the cells. 

Now go to Conditional Formatting >> Highlight Cells Rules >> Duplicate Values. 

Now in the small dialog, select the option "Duplicate" and choose what color you want. You will see in the background that the duplicate values are highlighted. 

Now you have the list of Duplicate cells, to continue what ever you wanted to do with them. 

Do write if you need more such tips and tricks in Excel. 

Google Voice - Tips and Tricks - Part 1

I confess - I love Google Voice. Why? Many reasons; this post may answer a little.

Chrome has been my browser from more than a year. Experiment with browsers, and after the much easier add-on install and uninstall was introduced (I'm talking in reference to Firefox here) I've stayed with Chrome.

Now Google has their own Chrome Extension. Those of you familiar with Chrome, might know the difference between App and Extension. If not, that's another post.

How to install: 

1. Go to your Chrome Web Store, and type Google Voice.

2. Search for this below extension, and install. 

3. After the installation, you should see this icon in your extension section. 

4. Done. 

What I love about it -- 

• I can call and text to my friends from any web page, and not touch my phone. 
• I don't need to dial any phone number on any webpage on my phone. I just need to click on the number and it dials to my phone, and then connects it to the destination number. Especially useful when I hunt numbers for Customer Care and need to look at the screen and then dial the number on my phone. I can either connect it to my linked phone on Google Voice or GTalk. Cool!
• Text conversations show up like chat in the pop-up. So its easy to follow what you replied to. 

Now here is what it looks like when you click on it. 

Some random webpage. See how phone number is hyper-linked. Clicking on it will give this smaller pop-up to connect.

Note: You need to have a Google Voice account for this to work, and need to be logged into your Google account on Chrome. I don't think this will work outside of USA, as Google Voice only works here. If you live outside USA, let me know if this works. 

I will write in more tips and tricks soon. Questions/Comments/Requests are always welcome. 

Note: I'm not paid by anyone to promote any product. This is something which I like and use everyday, and think its worth sharing. 

Concatenate Double Quotes in Excel

After my post on Concatenation in Excel, there was a question on how to concatenate Double Quotes (") in the formula. You might think it's complicated, but its actually very simple.

Just insert two double quotes inside the parent double quote to display one double quote in the final result.

Confusing? Let me explain.

Assume, this is how your spreadsheet is:

So to get our result as above, we need to use the formula as -

Now carefully count the number of double quotes in the formula, and see how it reflects in the Result cell.
Hope this helps.