All articles with 'mid()' Tag

Formula Forensics 042: Reverse Text – A Formulaic Solution

Published on Jan 4, 2017 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 042: Reverse Text – A Formulaic Solution

Use the new Textjoin() function to reverse a string of characters suing a formulaic solution.

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 »

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 4.)

Published on Jul 22, 2013 in Formula Challenges, Posts by Jeff
Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 4.)

Welcome back to another gripping episode of “When good formulas go GREAT”.  Just like the immortal combatants in the classic 1986 movie Highlander, over the last three posts in this series our Excel nerds have been locked in an ages-old battle to decapitate. A text-string, that is. Not each other. So far we have seen […]

Continue »

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Published on Jul 19, 2013 in Formula Challenges, Posts by Jeff
Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 3.)

Welcome back to yet another gripping episode of “When good formulas go GREAT”. We’re working up the singles charts to the number one hit “Formula Challenge 001 – Return everything in string after first block of numbers” By way of a quick refresher, this formula challenge calls for a formula to return a substring from […]

Continue »

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 2.)

Published on Jul 17, 2013 in Formula Challenges, Posts by Jeff

Today we look at the third place winner from our inaugural Formula Challenge, a neat solution provided by Sajan.

Continue »

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 1.)

Published on Jul 16, 2013 in Formula Challenges, Posts by Jeff
Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 1.)

This new series introduces a new author to Chandoo.org, Jeff.
Jeff will pose a number of Excel based Formula Challenges and then explain how some of the solutions work in a manner similar to the Formula Forensics series.

In this first post of the series we look at how to extract a string from the right of a block of Numbers in a larger string.

Continue »

Unlock “Angy Formulas” – an Angry Birds like game hidden in Excel using this trick!!!

Published on Apr 1, 2013 in excel apps
Unlock “Angy Formulas” – an Angry Birds like game hidden in Excel using this trick!!!

Sometimes you think you know something and then suddenly you are surprised. Yesterday was such a moment for me. I have been using Excel for almost a decade now. So naturally I assumed that I know it well. But then yesterday, while doing something I stumbled on a strange screen in Excel that looked like very popular Angry birds game. So I got searching. But there was no mention of it anywhere on net. Then I asked my friend Rollf ‘O’ Pai, who is in Micros0ft Execl team. First he denied such a thing. But we knew each other so well that he could never lie to me. So he confided. He told me what I had suspected for several years.

There is an Angry birds like video game buried in Excel!!! It was meant to be an Easter egg in Excel 2010 (and 2013), but due to backlash from senior management no one ever published the details about it.

So I asked him “How do I unlock it?”. Rollf ‘O’ Pai asked me to never reveal it to anyone and then told me the recipe.

Once I unlocked I could not believe how cool it is!

Read on to understand how to unlock this game.

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 »

Formula Forensics No. 027 – Remove Leading Zeroes

Published on Aug 23, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 027 – Remove Leading Zeroes

Today in Formula Forensics we look at how to strip away leading zeroes from strings with this simple array formula

Continue »

Formula Forensic No. 021 – Find the 4th Slash !

Published on May 17, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensic No. 021 – Find the 4th Slash !

No, Not that Slash !

How do I find the 4th Slash in a text string?
Today at Formula Forensics we take a look at 4 different methods.

Continue »

Formula Forensic No 019. Converting uneven Text Strings to Time

Published on Apr 19, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensic No 019. Converting uneven Text Strings to Time

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

Continue »

People & Websites that Helped me in 2011 [Thank you message]

Published on Dec 28, 2011 in blogging

2011 has been the most awesome year since we started Chandoo.org.

Without doubt, the credit goes to our community – that is you.

I feel very fortunate to have you in our community. You inspire me to learn & share. I know I said this several times, but everytime, I feel like I have not done enough. Thank you so very much.

Apart from our little community, I feel thankful to many others, who have helped me through out 2011 to make you awesome in Excel. In this post, I am highlighting them & expressing my gratitude.

Continue »

Formula Forensics 006. Palindromes

Published on Dec 15, 2011 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 006. Palindromes

Palindrome: – A palindrome is a word, phrase, number, or other sequence of units that can be read the same way in either direction, with general allowances for adjustments to punctuation and word dividers (“Wikipedia”).

This week in Formula Forensics we’re having a look at determining if a cell contains a palindrome.

Continue »

Check if a Word or Phrase is Palindrome using Excel Formulas [Weekend Fun]

Published on Aug 12, 2011 in Excel Howtos, Learn Excel
Check if a Word or Phrase is Palindrome using Excel Formulas [Weekend Fun]

The other day, while I was putting my kids to sleep, this idea came to me. How do I check if a cell contains a palindrome, using Excel formulas?

Next morning, I wrestled with excel for about 20 minutes and boom, the formula is ready.

But what is a palindrome?
A palindrome is a word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!
So, to check if a cell contains palindrome, we need to reverse the cell contents and see if both original and reverse are the same.

Continue »

Calculating Sum of Digits in a Number using Array Formulas [for fun]

Published on Mar 18, 2011 in Excel Howtos
Calculating Sum of Digits in a Number using Array Formulas [for fun]

Here is a fun formula to write.

Given a number in cell, I want you to find the sum of digits in it. So, for eg. if you have the number 3584398594 in a cell, the sum would be =3+5+8+4+3+9+8+5+9+4, equal to 58.

Now, how would you write a formula to find this sum automatically based on the number entered in the cell?

Go ahead and figure it out. If you can, come back and check your answer with mine below.

Continue »