Sat 28 Jun 2014

Tips & Tricks 26 - Fill in the data from non blank cells to blank cells down

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

Suppose you have data like below and you want to fill in blank rows with data with non blank row up. Here, in range A3:A5, you want value A to appear, in range B7:B9, you want value B to appear and so on.

You need this kind of situation, mostly, when you are making pivot which demands that all rows should have the data.

1

You need to follow following steps -

1. ALT+EGSK and Enter. This will select all your blank cells.

2. Type "=" (without double quotes) and press up Arrow.

3. CTRL+Enter

Now you will have cells filled as you wanted. You will notice that those blank cells are carrying the formula = A2, =A3 etc.

4. To convert them to static values, select you column A, Copy and Paste Special Values.

Sat 28 Jun 2014

Tips & Tricks 25 - Extract Integer and Decimal Portion of a Number

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

To extract Integer portion, one of the below can be used -

=INT(A1)
=TRUNC(A1)

Positive value in A1 - If A1 contains 84.65, then answer would be 84.
Negative value in A1 - If A1 contains -24.39, then answer would be -24.

If you want only +ve value whether value in A1 is -ve or +ve, the formula can have many variants.

=INT(A1)*SIGN(A1)  OR =TRUNC(A1)*SIGN(A1)
=INT(ABS(A1)) OR =TRUNC(ABS(A1))
=ABS(INT(A1)) OR = ABS(TRUNC(A1))

To extract Decimal portion -

=MOD(ABS(A1),1)
=ABS(A1)-INT(ABS(A1))

Positive value in A1 - If A1 contains 84.65, then answer would be 0.65.
Negative value in A1 - If A1 contains -24.39, then answer would be 0.39.

Sat 28 Jun 2014

Tips & Tricks 24 - Extract the Initial of Middle Name

By |Saturday, June 28th, 2014|Categories: Tips and Tricks|Tags: , , |1 Comment

Suppose, you have a name John Doe Smith and you want to show D as middle initial. Assuming, your data is in A1, you may use following formula

=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"")

If name is of 2 or 1 words, the result will be blank. This works on 3 words name only as middle can be decided only for 3 words name.

Sat 28 Jun 2014

Tips & Tricks 23 - Extract Last Name from Full Name

By |Saturday, June 28th, 2014|Categories: Tips and Tricks|Tags: , , , |1 Comment

Suppose you have a name like John Doe Smith in Cell A1. You want to extract the last name from the string.

1. Formula Way

Put following formula

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

2. Manual Way

Manual way is very handy when you have a large data set.

1. Select your column
2. CTRL+H
3. In Find What: box, put "* " without quotes (this is asterisk followed by a space character)
4. Replace All
5. You have only last name now.

Sat 28 Jun 2014

Tips & Tricks 22 - Extract First Name from Full Name

By |Saturday, June 28th, 2014|Categories: Tips and Tricks|Tags: , , , , |1 Comment

Suppose you have a name John Doe Smith and you want to extract the first name only. Put following formula assuming full name is in Cell A1.

Formula Way

=LEFT(A1,FIND(" ",A1&" ")-1)

Manual Way

Manual way will be useful when you have a long list of names.

A. Easy Way - Find and Replace Way

1. Select the column.
2. CTRL+H
3. In Find What: box, put a space followed by * (Hence, we need to put " *" without quotes)
4. Replace All
5. Now, you will have only First Name.

B. Text to Columns Way

1. Select the column
2. Data tab > Text to Columns
3. On Step 1 of 3, check Delimited and Next
4. On Step 2 of 3, check Space and Next
5. On Step 3 of 3, select 2nd column and check "Do not import column (skip). Do it for 3rd column also.
6. Finish and you have only first names in your column.

1

1

Sat 21 Jun 2014

Tips & Tricks 21 - Count only Alphabets in a String

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you have a string "Ab?gh123def%h*" and you want to count only Aphabets.

Suppose your string is in A1, put following formula for this.

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))

OR

=SUMPRODUCT(--(ABS(77.5-CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))

OR

=SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=
{"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"}))

Sat 21 Jun 2014

Tips & Tricks 20 - Count Numbers in a String

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , , |0 Comments

Suppose you have a string "abc123def43cd" and you want to count numbers in this.

If your string is in A1, use following formula -

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))

OR

=SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

Sat 21 Jun 2014

Tips & Tricks 19 - Count Non Numbers in a String

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , , , |0 Comments

Suppose you have a string "abc123def45cd" and you want to count non numbers in this.

If your string is in A1, use following formula in A1

=IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(--NOT(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))

Sat 21 Jun 2014

Tips & Tricks 18 - Count No. of Unique Values

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , |0 Comments

Use following formula to count no. of unique values -

=SUMPRODUCT((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))

Credit - There are various ways to accomplish above. I knew all other formulas. But the above formula is the sleekest. I learnt it from a posting to Excel Microsoft Community.

Sat 21 Jun 2014

Tips & Tricks 17 - Count No. of Cells which are containing only Characters

By |Saturday, June 21st, 2014|Categories: Tips and Tricks|Tags: , |0 Comments

Hence, if your cell is having a number 2.23, it will not be counted as it is a number.

Use below formula considering your range is A1:A10

=COUNTIF(A1:A10,"*")