Excel Tricks

To extract the last numbers in the text string (note: only if the string ends with numbers):
=--MID(A5, LOOKUP(2,1/MID(A5,1024-ROW(INDIRECT("1:1024")),1),1024-ROW(INDIRECT("1:1024")) ),20)

To extract the first text string if string ends with a numeric:
=LEFT(F2,FIND(--MID(F2, LOOKUP(2,1/MID(F2,1024-ROW(INDIRECT("1:1024")),1),1024-ROW(INDIRECT("1:1024")) ),20),F2)-1)

To extract the numbers in a package without the "G":
=MID(D2,ROW($1:$9),2)&--MID(D2, LOOKUP(2,1/MID(D2,1024-ROW(INDIRECT("1:1024")),1),1024-ROW(INDIRECT("1:1024")) ),20)

To extract the numbers in a package without the "G" in a TABLE:
=MID(Table1[[#This Row],[Pkg]],ROW($1:$9),2)&--MID(Table1[[#This Row],[Pkg]], LOOKUP(2,1/MID(Table1[[#This Row],[Pkg]],1024-ROW(INDIRECT("1:1024")),1),1024-ROW(INDIRECT("1:1024")) ),20)

To extract the first numerics and stops at the alpha:
=LOOKUP(99^99,--("0"&MID(D2,1,ROW($1:$10000))))

To extract numbers only. You must enter these formulas as arrays by pressing CTRL+SHIFT+ENTER.
=1MID(A5,MATCH(TRUE,ISNUMBER(1MID(A5,ROW($1:$9),1)),0),COUNT(1*MID(A5,ROW($1:$9),1)))

To transpose columns to rows:
=OFFSET($D4,COLUMNS($D4:D4)-1+(ROWS(4:4)-1)*4,0)

Remove apostrophe

  1. Add a column next to the column of data
  2. Use Data -> Text to Column and delimited to remove the first character.

Remove error #value, #n/a in cells:
=IF(ISERROR(A1,"-",A1)

To add columns for a certain criteria only:
=SUMIF(A1:A10,"DDR",B1:B10)

To create distribution:
=frequency(range of raw data, range of criteria) *** Place this at the starting point where distribution will be generated.

  • press range where to put the distribution, press F2, then CNTL-SHFT-ENTER

To match a string in an array (partial match only):
=INDEX($B$2:$B$566,MATCH(F2,$C$2:$C$566,1)+2,1)

To match a string in an array:
=IF(FIND(F2,O2)>0,INDEX($D$2:$D$43,MATCH(F2,$D$2:$D$43,1)+1,1),0)

Remove blank spaces:
=TRIM(A2)

Remove the last characters of the string (in this case, remove the last 1 character):
=LEFT(C1,LEN(C1)-1)

Remove the first characters of the string (in this case, remove the first 2 characters):
=RIGHT(A2,LEN(A2)-2)

Extract the first 2 charaters of the string (in this case, show the frist 2 characters):
=LEFT(C1,2)

Extract the last 1 charater of the string (in this case, show the last 1 character):
=RIGHT(C1,1)

To count how many times number 1 occurs in a row:
=COUNTIF(B1:G1,1)

To add "G" at the middle of a string:
=LEFT(G4,2)&"G"&--MID(G4, LOOKUP(2,1/MID(G4,1024-ROW(INDIRECT("1:1024")),1),1024-ROW(INDIRECT("1:1024")) ),20)

Subtotals:
Function_num (includes hidden values) Function_num (ignores hidden values) Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Add blank spaces at the end:
=A1&rept(" ",20-len(A1))

Copy and fill blank cells below the cell in the column:

Start by selecting the empty cells:

  1. Select the cells in the column, starting in the row below the first entry in the column.
  2. Press F5
  3. Click the Special button
  4. Select Blanks, click OK

Enter the formula to copy the value:

  1. Type an equal sign
  2. Press the up arrow on the keyboard -- this will enter a reference to the cell above -- cell A2 in this example
  3. Hold the Ctrl key and press Enter -- this enters the formula in all selected cells

Change the formulas to values:
In order to sort or filter the data, the formulas must be changed to values.

  1. Select the entire column
  2. Choose Edit | Copy
  3. With the column still selected, choose Edit | Paste Special
  4. Select Values, click OK

Note: Do this carefully if there are other cells in the range which contain formulas.

http://www.contextures.com/xlDataEntry02.html

Count Unique cells only for Pivot later on:

=IF(COUNTIF($AS$2:AS2,AS2)=1,1,0)