Lost your smartphone – No worries now Google it.

Yeah today Google revealed a new feature of Google search- “To find your lost phone”.

All you got to do is type “find my phone” in the Google search box. Just that and yes google finds the location of your phone. You can also make it ring from the browser by clicking the “Ring” button. See below picture:

Google your lost phone - Techflames

Google your lost phone

I understand for this to work you need to have logged in to your computer’s browser with the same google account you use on your smartphone. Also your smartphone must be online. Else you cannot make it ring, just can see the last available location.

This would be very handy as I keep forgetting my phone in bathroom, bed, study… Now I can make it ring and find it easily.

I’ve used it and it works like a charm. Let me know what you think!

Text functions in Ms-Excel: Left, Find and Concatenate

In the last post on text functions we saw trim and clean. For those whow work with lots of data analysis and specially if the data is coming from various sources he gotta use more than just trim to transform the data into meaningful information. One of the most useful function that I use is “Left”. Suppose you have “UHG212″ in a cell A1 and you need to extract the first 3 characters from the cell in A2 then you use this formula in A2:

=left(a1,3)

This will give you the first 3 characters from left and you can drag this formula to apply it for the entire column. To extract text from right you use the similar one =right(a1,3). Well that was easy but consider this situation you got a list of values like the below in cells B1 and B2 respectively.

EXTT_223

MYTTTY_443

In the above 2 values the text part is a course code and the numeric is its id number so I want to extract only the text part. (Of course you can use text to columns.) so to get this with a formula I need to know what is the position of underscore in each entry. So I know of a formula which would give me that which is =find(“_”,b1,1) which would return 5 for EXTT_23. So now to extract the text part out of it, you mix the left formula and the find which would be like this:

=left(b1,find(“_”,b1,1)-1)

This would give me the answer EXTT and how does that work? The find formula gives me the result 5 and I am substracting one from it to make it 4. Now using left function to get the 4 characters from left.

Just drag the formula to the remaining cells and it would give you the required course codes for each entry.

The data for the same with formulas is in this file (techflames_practice.xlsm), you can download and check it for your reference.

Before I wrap up I would also like to tell little about concatenate with an example. Suppose you’ve got info,@,techflaemes.com in 3 cells a1,b1,c1 respectively and you gotta join them to make the email id info@techflames.com how would you do it? 2 Ways one is using the formula in say cell d1 which is =concatenate(a1,b1,c1) which will give you the result else the direct way would be this formula =a1&b1&c1 which also gives you this result. Ok now I leave with this thought what if you have 100 emails in a1 to a100 cells  and you want to make a distribution list i.e. join them using a coma in between. Are you gonna write a lengthy formula like this =a1&a2&a3&a4…till &a100. Well for such things you got vba which can help you do this quickly. Will post the code in VBA section very soon.

Cya until next post.

Remove empty rows from selection using VBA.

Often I come across this situation where I have a few empty cells in between. Like Consider a blank sheet and then you paste some values into it in Column “A”.  Like in A1: January, A2: February, A3,A4&A5 are blanks, then A6: March, A7,A8 are blank, A9: April, A10: May, A11,A12 and A13 are blanks….. and so on…

Imagine this sequence runs down to 80 or 100 rows then its a pain to delete the blank rows in between. A trendy excel user might use the paste special technique of skip blanks and accomplish this. But a more easier way is a single line of code that helps remove all blanks rows. The code goes like this.

sub deleteblanksrows()

Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End sub

Now copy this code and paste it in a module (click ALT+F11 to go to vba editor and from Insert menu click Module-then paste this in the blank white space on right).

Go back to excel and select the selection which contains blank rows say in our case above it would be range A1:A13. Go back to VBA editor by hitting ALT+F11 and then keep the cursor on the 2nd line of the code and hit f5 to run the code.

That is it switch back to excel and you will see all the blank rows are gone.

I work with unformatted  data a lot and I use this code quite often so I have placed this on the ribbon of my excel 2010.

Hope you will also benefit from this VBA snippet.