Formulas‎ > ‎

Formulas


Cell formula to display content, position and formatting of cell

posted Jun 11, 2011, 9:04 AM by Ayush Jain   [ updated Jun 11, 2011, 10:51 AM ]

Sometimes we have many questions in mind like :

How to know the cell protection status - Locked or Unlocked ?
How to know the width of the cell ?
How to know alignment of cell ?
How to identify row & Col number of cell ?
How to know the worksheet name, filename & full path of given cell ?
& many more questions

The answer of all above questions is CELL function of excel :
--------------------------------------------------------------------------------
DEFINITION:- This function examines a cell and returns the information about the formatting, location, or contents of the first cell
--------------------------------------------------------------------------------
SYNTAX :- '=CELL("TypeOfInfoRequired",CellToTest)

"TypeOfInfoRequired" is : address, col, color, contents, filename, format, parentheses, prefix, protect, row, type, width

CellToTest is the cell to be tested :- A3, A20, C15 . . . . . .. .
--------------------------------------------------------------------------------
EXAMPLES : Let's assume the CELL D3 in below examples

To know width of cell D3 :-                                         =CELL("width",D3)

To know the row number :-                                         =CELL("row",D3)

To know the column number :-                                    =CELL("col",D3)

To know the cell value or contents :-                            =CELL("contents",D3)

To know the cell address :-                                         =CELL("address",D3)

To know the filename containing the Cell :-                   =CELL("filename",D3)
(Returns the Full Path of file with worksheet name)

To know the cell protection :-                                       =CELL("protect",D3)
(shown as 1 for a locked, 0 for unlocked.)

To know the Type of Entry in the cell :                          =CELL("type",D3)
(Shown as b for blank, l for text, v for value.

To know the cell alignment :                                         =CELL("prefix",D3)
(Shown as ' for left, ^ for centre, " for right.
Nothing is shown for numeric entries.)

Download the file enclosed in the post for more examples
--------------------------------------------------------------------------------
APPLICATION OF THE FUNCTION :
Some of you must be wondering where we can you the cell function :

Before sending the file to user or client , you can make check list to validate the cell formatting , content and position using these formulas.

--------------------------------------------------------------------------------
BONUS :

How to extract the filename/workbookname from the fullpath returned by CELL function :

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

Extract email address from sentence in cell

posted Mar 26, 2011, 7:11 AM by Ayush Jain   [ updated Jun 11, 2011, 10:52 AM ]

Recently I wanted to extract email address from the sentence given in the cell.
I got the below formula perfectly working to get this done.

EXAMPLE :

1) Hello, abc@abc.com is my email address = abc@abc.com
2) send the ebook at this address  xyz@discussexcel.com = xyz@discussexcel.com

Formula is :
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))

Display Feet and Inches as 5' 8 1/2"

posted Jan 30, 2011, 1:05 AM by Ayush Jain   [ updated Mar 26, 2011, 7:06 AM ]


When converting metric distances to English units of feet or inches, you'll end up with a decimal value. But, you may want to display the value as feet and inches using the format 5' 8 1/2".

Let's say you have the decimal value 5.708333 for feet in cell A1. To display feet and inches, use INT(A1) to get the 5 and use A1-INT(A1) to get the remainder (inches). Multiple the remainder by 12 and then use the TEXT function to display the value as "# #/#" inches.

Here's the formula:
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/#")&CHAR(34)

Hidden text in Formula

posted Jan 30, 2011, 12:49 AM by Ayush Jain   [ updated Jan 30, 2011, 12:59 AM ]

By using N() function you can enter hidden text in the formula. It is helpful when you want to display any information along with formula in single Cell.

Example : =2018+1056-4*120+N("My Salary + Bonus - 4 weekly loan repayments")
Output := 2594

Note :- Ideally N function is used to convert Non Number value to Number like Dates to Serial Number, True to 1 & anything else to Zero. Hence, the N() function used not be used with text functions otherwise it will result in error.

Extract % from the Securities or any Text

posted Jan 29, 2011, 10:18 PM by Ayush Jain   [ updated Jan 29, 2011, 11:00 PM ]


In finance & banking transactions, The interest rate is used in the name of many securities & transactions...If you have this data in excel sheet , then you can filter out the interest rate in separate column using excel formulas . HERE IS HOW

You will have to use MID & FIND function to extract the % and TRIM function to remove the spaces.

 Security Interest Rate
Formula Used                                    
 ABB 07.60% 22JAN13
 07.60  =TRIM(MID(A2,FIND("%",A2,1)-5,5))
 08.80% STATE BKOF BK&JAI 05SEP11 FD
 08.80 =TRIM(MID(A3,FIND("%",A3,1)-5,5))
 NAT HSG BK 6.90% 30OCT12
 6.90 =TRIM(MID(A4,FIND("%",A4,1)-5,5))
 ICICI 0.00% 24AUG12 0.00 =TRIM(MID(A5,FIND("%",A5,1)-5,5))

Can you extract the date from these securities ?

If yes, please post in the comments.

Split First name & Surname

posted Jan 29, 2011, 12:07 AM by Ayush Jain   [ updated Jun 11, 2011, 10:54 AM ]

Do you want to split your text on the basis of text it contains?
Do you want to extract First name /Last Name from Full Name ?
Do you want to extract Middle name from Full Name ?

Then , You are at right place.

Text Functions are used to extract specific information from the cell.
One of the common example is to split the Forename & Surname of an individual.

The Text functions used are Left, Right, Find, Mid, Len.

Please see the snapshot below for examples.

 



Convert month in text to number

posted Jan 28, 2011, 11:48 PM by Ayush Jain   [ updated Jan 29, 2011, 9:34 PM ]

When we pull data from different sources i.e. SQL, txt file, CSV files...., The date columns are imported in text format because of their source data type or could be due to any other reason.

For example - You have data of month in text format with no fixed length(at least three characters) like Jan, Febr, March, Apr, May etc. & you want to convert them into number as 1,2,3,4,5.

In this case, the best formula you can use to convert month in text to number is
=Month(A1&1)
OR
=Month("Apr"&1)


You can also use this formula as sub function in Date function .
Example :
=DATE(2011, MONTH(A1&1),2)
The Result would be : 02/04/2011

Let us know if you know better solution !

1-7 of 7

Comments