All articles with 'left()' Tag

Are you in IT & use Excel often? This article explains top 10 formulas for IT professionals. Useful for project managers, IT analysts, Testing people and BAs.

We cover a 10 practical situations and explore various Excel formulas to solve them. Example workbook provides more details too.

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file.

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template that can convert VCF data to Excel worksheet.

I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this…

Help

Houston, We’ve Had a Problem!
In the initial emails requesting a solution to yesterday’s Formula Forensics, Chandoo’s solution, although Technically correct, Didn’t work ?
This post looks at the problem and what was wrong with the data causing the error.

It is almost 3:30 am now, I stayed awake for last 23 hours so that Excel School 2 can be ready for rolling. But that is no excuse for not having a post here. So here it goes. Excel has formulas for converting a bunch of text to UPPER, lower and Proper Cases. But not […]

Sometimes when we import data from another source in to excel, the dates are not imported properly. This can be due to any number of reasons. In this post, we will learn some tricks and ideas you can use to quickly convert text to dates.

Today we will learn to use Excel’s LEFT and FIND formulas. But what fun it is to learn a new formula on a Tuesday? So, we will actually learn to use these formulas to solve the problem: “extract the username from an email ID” How is an email ID structured? Any email ID contains 2 […]

Excel is very good for keeping track of your investments. Due to its grid nature, you can easily create a table of all the mutual fund holdings and monitor the latest NAVs (Net Asset Values) to see how your investments are doing. A while back we have posted a file on tracking mutual funds using excel. Today we are going to release an upgrade for that file.

Read the rest of this post to understand how this template works and download the free template.

It is no exaggeration that knowing excel formulas can give you a career boost. From someone starting at the long list of numbers, you can suddenly become a data god who can lookup, manipulate and analyze any spreadsheet.

So when our little excel blog hit the 5000 RSS Subscriber milestone, I celebrated the occasion by asking you to share an excel formula through twitter or comments with rest of us. And boy, what an excellent list of formula tips you have shared with us all.
Here is the complete list of entries for the twitter formula contest.

Do you know that it is not so straight forward to use credit card numbers in excel. Yes, excel uses a precision of 15 digits and thus when you enter a credit card number (16 digits) it is converted in to scientific format and the details are lost. Read on to learn the work around.

Often when you are writing code or testing an application, it might be helpful to have random phone numbers generated for you. Here is an excel formula that I use: =value(left(substitute(rand(),”0.”,””),10)) try these 2 instead (thanks to John) =RAND()*(9999999999-1000000000)+1000000000 =RANDBETWEEN(1000000000,9999999999) [needs analysis toolpack add-in or excel 2007] Even though it works most of the time […]