All articles with 'text processing' Tag

Extract the 10 digit number [formula homework]

Published on May 6, 2016 in Excel Challenges

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break  – CHAR(10) and you need to extract the number that is 10 digits long.

extract-10-digits

Go ahead and solve this riddle.

Continue »

Can you extract numbers from text – homework

Published on Oct 30, 2015 in Excel Challenges, Learn Excel

Here is a quick homework to keep you busy this weekend.

Can you extract number of days from below text.

Nov15 PUTS (23 days)
March15 TIKS (3 days)
March1 TIKS (25 days)
June11 TIKS (10 days)

Assume the data is from cell A1.

Your solution should return the following:
23
3
25
10

Post your answers (formulas, VBA code or Power Query M code) in the comments.

Continue »

Save a range as text file using VBA [tutorial]

Published on Oct 28, 2015 in VBA Macros
Save a range as text file using VBA [tutorial]

Last night I asked members of our Chandoo.org facebook page to share an Excel problem you are struggling with.  Francis asked,

How to save a file as .txt in vba without quotes? When I save as .txt, the file has got quotes inside of it. I used the code Print, but it didnt work because the file loses its delimitation.
Does anyone know how to solve this?

Let’s understand how to save a range as text and overcome the double quote problem.

Continue »

Use NUMBERVALUE() to convert European Number format

Published on Oct 6, 2015 in Excel Howtos, Learn Excel

If you deal with customers or colleagues in Europe, often you may see numbers like this:

  • 1.433.502,50
  • 9.324,00
  • 3,141593

When these numbers are pasted in Excel, they become text, because Excel can’t understand them.

Here is a simple way to convert the European numbers to regular ones.

Use NUMBERVALUE() Function.

Continue »

Case Sensitive Lookups

Published on Sep 7, 2015 in Excel Howtos
Case Sensitive Lookups

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

Continue »

How to find out if a text contains question? [Excel formulas]

Published on Jul 17, 2015 in Excel Howtos, Learn Excel
How to find out if a text contains question? [Excel formulas]

On Wednesday (15th July), I ran my first ever webinar, on a topic called, “How to be a BETTER Analyst?” (here is the replay link, in case you missed it).  It was a huge success. More than 1,100 people attend the live webinar and hundreds more watched the replay. As part of the webinar, we had interactive Q&A. Viewers posted their questions and I replied to as many of them as I can.

After the webinar, I wanted to make sure I covered all the questions. So I downloaded the chat history. There were more than 700 messages in it. And I am not in the mood to read line by line to find-out the questions. A good portion of chat messages were not questions but stuff like ‘hello everyone, I am from Idaho’, ‘Wow, Chandoo has beard!”, “Enjoying a beer in Belgium while watching webinar” etc. So I wanted a quick way to flag the messages as question or not.

Continue »

Export iPhone contacts to Excel using this free template

Published on Dec 2, 2014 in Excel Howtos, Learn Excel, Templates
Export iPhone contacts to Excel using this free template

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.

Continue »

Can you extract first name & last name from email address? [Formula Challenge]

Published on Feb 27, 2014 in Excel Challenges
Can you extract first name & last name from email address? [Formula Challenge]

Today lets rescue John Doe from John_doe@email.com.

Extract first & last name from email address

Given an email address in the format

firstnameany_non-alphabet.characterslastname@email.com

You need to extract first name & last name using formulas.

Continue »

Quickly combine text in multiple cells using this trick! [Formulas]

Published on Jan 13, 2014 in Excel Howtos
Quickly combine text in multiple cells using this trick! [Formulas]

Ever wondered how to go from a bunch of cells with text to one big combined text? Like shown above.

Well, there is a simple trick. One of our readers, Grant shared this with us. Read on to learn this.

Continue »

Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel
Top 10 Formulas for Aspiring Analysts

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

Can you find that pattern? [Homework]

Published on Dec 14, 2012 in Excel Challenges
Can you find that pattern? [Homework]

Are you ready for an Excel challenge?

Today, your job is very simple. Just find a pattern in a text and return corresponding value.

Your Homework:

In a range we have some resource types & their billing rates.

In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.

See above diagram.

Continue »

Even faster ways to Extract file name from path [quick tip]

Published on Oct 24, 2012 in Excel Howtos, VBA Macros
Even faster ways to Extract file name from path [quick tip]

The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.

1. Using find replace
2. Using text to columns
3. Using UDFs

Continue »

Extract file name from full path using formulas

Published on Oct 23, 2012 in Excel Howtos, Formula Forensics
Extract file name from full path using formulas

Today lets tackle a very familiar problem. You have a bunch of very long, complicated file names & paths. Your boss wants a list of files extracted from these paths, like below:

Of course nothing is impossible. You just need correct ingredients. I cannot help you with a strong cup of coffee, so go and get it. I will wait…

Back already? well, lets start the formula magic then.

Continue »

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

Using an Array Formula to Find and Count the Maximum Text Occurrences in a Range

Chandoo is off on Holidays and once again thrown me the keys to Chandoo.org.

So this week we’re going to:

  • Pull apart an Array Formula and see what’s under the hood;
  • Have a look at a technique for quickly developing Custom Number Formats &
  • Look at how we can use HTML Codes in posts at Chandoo.org to spice up your questions and answers.

Today is Tarun’s Problem

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?”

Today we pull apart an answer to this question and see how it works.

Continue »

An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial]

Published on Nov 24, 2010 in Charts and Graphs
An Excel Dashboard to Visualize 10,007 Comments [Dashboard Tutorial]

First some good news, On 21st November, 2010, our little blog received its 10,000th comment!

Thank you so much for making this happen.

Those of you reading chandoo.org for a while know my penchant for comments. I have learned a lot of excel tips & ideas just by reading the comments you posted on this blog. I think comments are one of the best parts of this blog. So, naturally, I wanted to celebrate this milestone, with something big & awesome.

My intention was to download all the 10,000+ comments and play with the data to come up with something outstanding, like a dashboard. It took me 2 days to conceptualize and create this beauty.

Continue »