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. 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.

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.  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,"*")