Excel formulas

This formulas are very helpful and nor very popular. I used them very often:

General formulas

Give current cell address

=ADDRESS(ROW();COLUMN()) - give current cell address;

Give current cell value

=INDIRECT(ADDRESS(ROW();COLUMN())) - give current cell value;

Vlookup search

Search the text of B1 in the range $A$2:$A$415 with exact match. If you found the result return column 1 otherwise N/A.

=VLOOKUP(B1,$A$1:$A$415,1,FALSE)

Custom search

In order the search to be propagated on all rows and not only on the current one you need to type this formula with CTRL + SHIFT + ENTER .
Searching for value of B14 in range $A$2:$A$15. You can use vlookup forluma instead of this one

=IF(SUM(--ISNUMBER(SEARCH(B14;$A$2:$A$15)))>0;"OK"; "NOK")

Random number between two numbers

The formula generates random numbers between the min and max value. Values will be refreshed at every change in the excel sheet. If you want to keep the generated values paste them as values.

=RANDBETWEEN(1,20)

Count if found

search and count for word test

=COUNTIF(A1:A100,"test")

search and count for any word or digit

=COUNTIF(A1:A100,"*")

Format

convert date

from YYYYMMDD to DD/MM/YYYY

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Adding zeroes

Example:

"1" -> "00001"
"23" -> "00023"
"12345" -> "12345"

Formula:

=TEXT(A1,"00000")

Convert to Java string

Very useful if you have large dataset in excel and you need to used it like list in Java.
Result:

text - > "text",

Formula:

=""""&H1&""","

Convert to SQL string

Very useful if you have large dataset in excel and you need to used it like list in SQL.

text - > 'text',

Formula:

="'"&H1&"',"

Remove last charecter

=LEFT(A2,LEN(A2)-1)