Sun 03 Sep 2017

Tips & Tricks 162 - Convert a Month Name to Month Number

By |Sunday, September 03rd, 2017|Categories: Tips and Tricks||0 Comments

Suppose, you have text denoting month in cell A1. Let's say A1 = "Sep" or A1="September", then you can use following formula to convert this to month number

=MONTH(1&A1)

=--TEXT(1&A1,"m")

In case, cell A1 contains  the partial month name say "Septe", then in place of A1 in the above formulas, you can write LEFT(A1,3).

Sat 06 May 2017

This is one single document which contains close to 100 formulas dealing with various situations. Useful for Intermediate and Advanced users.

Tue 07 Jun 2016

Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

By |Tuesday, June 07th, 2016|Categories: Solutions|Tags: , , , , , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 42 – Split a Sentence Into Words in Different Cells

Enter following formula in B1 and drag to the right and down.

=IFERROR(MID(\$A1,FIND("@",SUBSTITUTE(" "&\$A1&" "," ","@",COLUMNS(\$A:A))),
FIND("@",SUBSTITUTE(" "&\$A1&" "," ","@",COLUMNS(\$A:B)))-1-
FIND("@",SUBSTITUTE(" "&\$A1&" "," ","@",COLUMNS(\$A:A)))),"")

The workbook containing this solution can be downloaded from Solution - Challenge 42 – Split a Sentence Into Words in Different Cells

Sat 21 May 2016

Article 39 - Sorting in Excel

By |Saturday, May 21st, 2016|Categories: Articles||0 Comments

First thing first – Excel doesn’t sort as per ASCII character codes which we generally expect. It has its own sorting logic which is detailed below for Ascending sort (reverse the same for Descending sort).

The order followed in an Ascending sort is Number > Text > Alphanumeric text > Logical values > Error values > Blanks

Sat 07 May 2016

Challenge 42 - Split a Sentence Into Words in Different Cells

By |Saturday, May 07th, 2016|Categories: Challenges|Tags: , , , , , , , , |0 Comments

This time the challenge before you is to write a formula that if a sentence is given in A1, your formula if dragged to the right should split the sentence into words.

Remember, it has to be a single formula which should be put into B1 and dragged to the right.

See the examples below -

A workbook containing this challenge can be downloaded from Challenge 42 - Split a Sentence Into Words in Different Cells

The solution to above challenge will be published on 7-Jun-16.

Sat 29 Aug 2015

Article 24 - Convert a Number to a Month Name

By |Saturday, August 29th, 2015|Categories: Articles|Tags: , , , , , , |2 Comments

Use below formula to generate named 3 lettered month like Jan, Feb....Dec

=TEXT(A1*30,"mmm")

Replace "mmm" with "mmmm" to generate full name of the month like January, February....December in any of the formulas in this post.

Sat 16 Aug 2014

Tips & Tricks 42 - Inputting Numbers Larger than 15 Digits

By |Saturday, August 16th, 2014|Categories: Tips and Tricks|Tags: , , , , |0 Comments

Sometimes, you may have a situation where you need to input numbers having more than 15 digits. For example, Bank Account Nos.

What is 15 Digits Limit - Excel has number precision of 15 digits, it means that if you input numbers > 15 digits, 16 digits onwards, it will be 0s.

Hence 123456789123456789 will be 123456789123456000.

Hence, you will need to enter that in Text format only. There are 2 ways.

1. Precede the number by apostrophe sign ('). Hence '123456789123456789 will be shown as 123456789123456789 in the cell. In Formula Bar, you can notice ' sign not in cell.

2. Take right click>Format Cells>Text in Number tab. It will format your cells as Text. Now you can input larger than 15 digits number in this.

Sat 07 Jun 2014

Tips & Tricks 11 - Convert a Number to Named Month

By |Saturday, June 07th, 2014|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

I am deleting this post as I have written a proper article on this and below is a link to this

http://eforexcel.com/wp/article-24-convert-a-number-to-a-month-name/

Mon 05 May 2014

Article 1 - Convert Number Format into Text Format

By |Monday, May 05th, 2014|Categories: Articles||2 Comments

The very first post which I want to write about is conversion of numbers into text. For example, if have a numeric value of 2.23, it should be converted to text value of 2.23. We may need this in many data manipulation scenarios. I will not go into why we need this but directly jump into writing about this topic.

In my view, there are always 4 ways to achieve anything in Excel

1. Formula
2. Manual
3. VBA
4. Hybrid (Combining minimum 2 of above methods)

Let's touch on all 4 ways. For the purpose of this article, let's assume a numeric value of 2.23 is contained in cell A1.

But first, after conversion, how will you know that you number is converted into text. You can use any one method from below -

1. Numbers are aligned right and Texts are aligned left by default unless you change the alignment. Hence, after conversion, you will notice that right aligned number is changed to left aligned number (which is text now, in fact). {Note - This is not a foolproof method}

2. Use the formula ISTEXT. If converted Number is contained in B1, use formula = ISTEXT(B1). If result is TRUE, it is Text. This is the best and foolproof method as it doesn't depend upon alignment.