Tag: Excel Cell

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table’s value. For the DATA table, we have a range of value values in the form of an array that is sent to the formula as input, and therefore the formula returns the first cell value.

We will be using the following functions in this article:

The next sections include information on the functions mentioned before that we will use.

ADDRESS retrieves the address of a cell using the row and column numbers.

Syntax:

=ADDRESS(row_num, col_num, [abs_num] )
  • Row_num: The Row number
  • Col_num: The Column number
  • [abs num]: [Optional] integer, one of the following values: 1, 2, 3, or 4. If this parameter is ignored or set to 1, the returned address will be absolute, e.g. $A$1.

Now we’ll combine these functions into a formula. We’ll begin with a data table. We need to determine the data table’s initial cell address.

Utilize the following formula:

=ADDRESS(ROW(table_array), COLUMN(table_array))

Variables:

  • table array: array representation of a data table

Examples:

Let us validate this formula by applying it to an example.

Here we have a data table and we need to use the formula to get the first cell address in the same data table.

Address of first cell in range1

Use the following Formula:

= ADDRESS(ROW(A1:E9), COLUMN(A1:E9))

Address of first cell in range1

Note: A1:E9 is array of tables

Let’s See How This Formula Works:

= COLUMN(A1:E9)

Address of first cell in range1

The COLUMN function returns the value of the first column in the table array.

= ROW(A1:E9)

Address of first cell in range1

The ROW function returns the index of the first row in the table array.

The ADDRESS function accepts the row number and column value parameters and returns the row’s and column’s absolute references.

The array sent to the function is denoted by cell reference. To get the result, press Enter.

As you can see, the formula works correctly since it delivers the value for the first cell in the data table.

Alternatively, you may use the CELL function:

CELL is an Excel built-in information function. Excel’s Cell function saves all the data in a specified cell and returns the cell’s info type. Using the follow formula:

=CELL("address", [array_reference])

or

=CELL("address",INDEX(A1:E9,1,1))

Address of first cell in range1

Note:

  • [array_reference] : table array given as reference.

As seen by the above calculation, you may use the first cell value as a reference.

The INDEX function can be used to get cell reference to the first cell in the given range by giving INDEX 1 for row number and 1 for column number.

Address of first cell in range1

The ADDRESS function may be used in conjunction with the ROW, COLUMN, and MIN functions to get the address of the first cell in a range. I hope this post on finding the get address of first cell in range in Excel was informative. It’s extremely easy and very helpful for people who use excel regularly.

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel ADDRESS function
    The Excel ADDRESS function returns a reference as a text string to a single cell.The syntax of the ADDRESS function is as below:=ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])….
  • Excel COLUMN function
    The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

How to Get Cell Address of Max or Min Value in a Range in Excel

This post will guide you how to return the cell address of a maximum cell value from a given range of cells with a formula in Excel. How do I Find the minimum value from a given range and return its cell address with formula in Excel 2010/2013/2016.

Return Cell Address of Max Value in a Range

Assuming that you have a list of data in range B1:B5, which contain some numbers, and you need to find the largest value from this range, and return the cell address. How to do it. You can use a formula based on the CELL function, the INDEX function, the MATCH function and the MAX function to get the cell address of the largest value in range. Like this:

=CELL("address", INDEX(B1:B5,MATCH(MAX(B1:B5),B1:B5,0)))

Type this formula into a blank cell and press Enter key on your keyboard. It would return the cell address of the maximum value in range B1:B5.

get cell address of max value1

You can change the range B1:B5 as you need to get the cell address of the maximum value in a list.

Return Cell Address of Min Value in a Range

If you want to find the smallest value in range B1:B5, and return the cell address. You can use the following formula based on the CELL function, the INDEX function, the MATCH function and the MIN function. Like this:

=CELL("address", INDEX(B1:B5,MATCH(MIN(B1:B5),B1:B5,0))) 

Type this formula into a blank cell and press Enter key on your keyboard. It would return the cell address of the minimum value in range B1:B5.

get cell address of max value2

Let’s see how those two formula work:

The INDEX/MATCH function will return the reference of the Maximum or minimum value from a range. And the Cell function returns the cells address。

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

How to Return Cell Address Instead of Value in Excel

This post will guide you how to lookup value and return cell reference containing that value in Excel. How do I return cell address from a lookup result with a formula in Excel.

Return Cell Address From Lookup Result

Normally, you can use INDEX/MATCH function to lookup a value in a given range of cells, and returns cell value. If you want to return cell address instead of cell value in your formula, how to do it. In this case, you can use another function call CELL in combination with INDEX/MATCH to create a new formula to lookup a value and return cell address.

Assuming that you have a list of data in range A1:C9, and you need to look up a text string called “excel“, and return cell address of the corresponding cell value in second column. You can use the following formula:

=CELL("address",INDEX(A1:C9, MATCH("excel",A2:A9,0),2))

Type this formula into a blank cell and press Enter key on your keyboard.

return cell address intead of value1

Related Functions

  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

Get Workbook Path Only

This post will guide you how to get the current workbook path in Excel. How do I insert workbook path only into a cell with a formula in Excel.

Get workbook path with Document Location

You can get the workbook path from the document location feature, and copy the location to a cell. Do the following steps:

#1 go to File tab, and click Options menu from the popup menu list. And the Excel Options dialog will open.

#2 click Quick Access Toolbar option, and select All commands from the drop down list of the Choose commands from. And then select Document Location value, click Add button to add it into Quick Access Toolbar.

get workbook path1

#3 click OK button. The workbook path is displayed in the Quick Access toolbar. Press Ctrl +C to copy it.

get workbook path2

Get workbook path with Formula

You can also use a formula based on the LEFT function, the Cell function, and the FIND function to get workbook path only in Excel.  Just like this:

=LEFT(CELL("filename",B1),FIND("[",CELL("filename",B1))-1)

Type this formula into a cell and then press Enter key.

get workbook path3

Let’s see how this formula works:

The Cell function will be used to get the full name and path of the workbook file.

The Find function will return the location number of the first left square bracket.

The left function will extract the workbook path based on the number returned by the Find function.

Related Functions

    • Excel Find function
      The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
    • Excel LEFT function
      The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
    • Excel CELL function
      The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

 

Insert The File Path and Filename into Cell

This post will guide you how to insert the file path and filename into a cell in Excel. Or how to add a file path only in a specified cell with a formula in Excel. Or how to add the current workbook name into a cell in Excel. How do I insert the current worksheet name into a Cell with a formula.

Insert File Path and Filename into Cell

If you want to insert a file path and filename into a cell in your current worksheet, you can use the CELL function to create a formual. Just like this:

=CELL("filename")

Type this formula into a blank cell, and then press Enter key in your keyboard.

insert filepath filename in cell1

This fromula will return the entire path, filename, and tab name of the current worksheet.

If you want to strip out the brackets and the sheet name, you can use the following formula based on the SUBSTITUTE function, the LEFT function, the CELL function and the FIND function:

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

insert filepath filename in cell2

Insert Filename Only into Cell

If you only want to insert the workbook name into a cell, you can use a formula based on the MID function, the SEARCH function, and the CELL function. Just like this:

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

Type this formula into a blank cell and then press Enter key.

insert filepath filename in cell3

You will see this formula will return only the current workbook name in Cell.

Insert Filepath Only into Cell

If you just want to insert the File Path into a cell, you can create a formula based on the LEFT function, the Cell function,and the FIND function. Just like this:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Type this formula into a blank cell and then press Enter key to apply this formula.

insert filepath filename in cell4

This formula will return only the File path of the current workbook.

Insert Worksheet Name into Cell

If you want to get the worksheet name only and then insert it into one cell, you can create a formula based on the RIGHT function, the LEN function, the CELL function and the FIND function. Just like this:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

Type this formula into a blank cell and then press Enter key to apply this formula.

insert filepath filename in cell5

This formula will return only the current worksheet name in a cell.

Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num]) ….
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…

 

Reference Tab Name

This post will guide you how to reference a tab name in one cell in Excel. How do I reference a tab name in cell with a formula in Excel. Can anyone tell me the formula on displaying the name of the current worksheet name in a cell on the Sheet.

Reference Tab Name with a Formula

To reference a tab name in a cell with a formula in Excel, you can create a formula based on the MID function, the Cell function, and the FIND function.

Assuming that you want to reference the current worksheet name in cell B1, you can use the following formula to achieve it. Like this:

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255)

Type this formula into the formula box of cell B1, and press Enter key to apply this formula.

reference tab name1

You will see that the tab name of the current worksheet has been displayed into the cell B1.

Reference Tab Name with a User Defined Function

If you want to reference the sheet tab name quickly, you can write a User defined function in Excel VBA. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

reference tab name2

Function RefTabName()

  RefTabName = ActiveSheet.Name

End Function

#5 back to the current worksheet, try to enter the below formula in Cell B1.

=RefTabName()

reference tab name3

You will see the sheet tab name is displayed in cell B1.

Related Functions

  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

Quickly Get Sheet Name

This post explains that how to get the sheet name only in your current worksheet in excel. How do I insert the current worksheet’s name into one cell quickly.

Get Sheet Name with Formula

If you want to quickly get current worksheet name only, then inert it into one cell. You can use a formula based on the MID function in combination with the FIND function and the Cell function.

You can type the following formula into the formula box of cell B1, then press enter key.

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255)

get sheet name1

Let’s see how this formula works:

=CELL(“filename”,B1)

The cell function will get the full file name and its path of your current worksheet. The result look like this: C:\ [book1.xlsm]sheet1

get sheet name2

=FIND(“]”,CELL(“filename”,B1))+1

The FIND function will get the first position of the character “]”, then passed it into the MID function to extract the sheet name.

get sheet name3

Get Sheet Name with VBA

You can also use an Excel VBA Macro to get all worksheet’s name and insert those sheet name into the different cells. Just do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

get sheet name4

#5 back to the current worksheet, then run the above excel macro. Click Run button.

get sheet name5

#6 Lets see the result:

get sheet name6

Related Functions

  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

How to Get Cell Address of a Lookup Value

This post explains that how to lookup a value from a range or array, then return cell address from a lookup result in excel. How to get the cell reference of lookup result in a range.

Get Cell Address (reference) of a Lookup Value

If you want to lookup a value in a range or column and return the cell address of the first match of lookup value, you can use a combination with the CELL function, the INDEX function and the MATCH function.

In the previous post, we talked that how to lookup a value in a column, you can use the following formula:

=INDEX(A2:A5,MATCH("jenny",A2:A5,0))

get cell address of lookup value1

If the position of the first occurrence of the lookup value “jenny” is returned by the MATCH function, then this formula returns string “jenny”. Or it returns a #N/A error.

To get the cell address or return the relative cell absolute reference from a lookup result that returned by the above INDEX function, you can use CELL function to get the cell address as follows:

=CELL("address",INDEX(A2:A5,MATCH("jenny",A2:A5,0)))

get cell address of lookup value 2

The CELL function will return the address of a cell that containing lookup value. So it returns $A$3.

Related Formulas

  • Find the nth Smallest Value
    You can use the SMALL function to get the 1st, 2nd, 3rd, or nth smallest value in an array or range. Also you can use the SMALL function within the INDEX function to extract the relative value of the same row…
  • Get the First Match in Two Excel Ranges
    If you want to find the first match between two excel ranges, you can use a combination of the INDEX function, the MATCH function and COUNTIF function to create a new formula….
  • Extract a List of Unique Values from a Column Range
    f you want to extract a list of unique items from a column or range, you can use a combination of the IFERROR function, the INDEX function, the MATCH function and the COUNTIF function to create an array formula…..

Related Functions

  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH function
    The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….

 

Get Path and Workbook name only

Generally, the Cell function returns the full file name contain workbook and worksheet name and it is absolute path.

If you want to get the workbook name and its path only without worksheet name, you can use a combination of the SUBSTITUTE function, the LEFT function, the CELL function and the FIND function.

=SUBSTITUTE( LEFT( CELL("filename",B1), FIND("]",CELL("filename",B1))-1), "[","")

The CELL function will get the full information of the current worksheet contain the full file name and its path.

=CELL("filename",B1)

Get the Current Worksheet Name only11

As you just want to extract the string that contain only the path and workbook name, so using the FIND function to get the position of the right bracket symbol. Then you can get the length of the path and workbook name only using the below Find formula:

=FIND("]",CELL("filename",B1))-1

Get Path and Workbook name only1

The LEFT function will get the leftmost the text characters based on the length of the string returned by the above FIND formula.

=LEFT( CELL("filename",B1), FIND("]",CELL("filename",B1))-1)

Get Path and Workbook name only2
Last, you still need to use SUBSTITUTE to replace the left bracket symbol within the result returned by the first CELL function.

=SUBSTITUTE( LEFT( CELL("filename",B1), FIND("]",CELL("filename",B1))-1), "[","")

Get Path and Workbook name only3

Related Formulas

  • Get the Current Worksheet Name only
    If you want to get the current worksheet name only in excel, you can use a combination of the MID function, the CELL function and FIND function.…
  • Get full File Name (workbook and worksheet) and Path
    In excel, you can get the current workbook name and it is absolute path using the CELL function.  Just refer to the following formula:=CELL(“filename”,B1).…
  • Get the Current Workbook Name
    If you want to get the name of the current workbook only, you can use a combination of the MID function, the CELL function and the FIND Function…

Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num]) ….

Get full File Name (workbook and worksheet) and Path

In excel, you can get the current workbook name and it is absolute path using the CELL function.  Just refer to the following formula:

=CELL("filename",B1)

Get the Current Worksheet Name only1

Enter the above formula into any of cells in the current worksheet, then you will get the workbook name and its path, it is similar with the below format:

path[book1.xlsx]sheet1

From the result returned by the Cell function, it contain the absolute path of the current workbook, the current workbook name and the current worksheet name.

The second argument of the CELL function is an optional. You can provide one cell reference, then it returns the full information of the worksheet that contain this provided cell. If the cell reference is not specified, then the CELL function will return the full information of the current worksheet.

Related Formulas

  • Get the Current Worksheet Name only
    If you want to get the current worksheet name only in excel, you can use a combination of the MID function, the CELL function and FIND function.…
  • Get the Current Workbook Name
    If you want to get the name of the current workbook only, you can use a combination of the MID function, the CELL function and the FIND Function..…

Related Functions

  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

Get the Current Worksheet Name only

If you want to get the current worksheet name only in excel, you can use a combination of the MID function, the CELL function and FIND function. You can use the below generic formula:

=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,255)

Get the Current Worksheet Name only11

The below explains how the above excel formula works:

The CELL function will get the full information of the current worksheet contain the full file name and its path.

=CELL(“filename”,B1)

Get the Current Worksheet Name only11

The FIND function will locate the position of the right bracket symbol in the result returned by CELL function.

So the starting position of the filename of the current workbook can be got from the below formula:

=FIND("]",CELL("filename",B1))+1

Get the Current Worksheet Name only11

The MID function will extract the worksheet name from a text string returned by CELL function at the position that returned by the FIND function.

Related Formulas

Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (sub string) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

Get the Current Workbook Name

This post explains how to get the current workbook name in excel.

Get the Current Workbook Name

In excel, the CELL function can get the information of the current worksheet, it will return the full file name and its path. If you want to get the name of the current workbook only, you can use a combination of the MID function, the CELL function and the FIND Function. Just refer to the below generic formula:

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

Let’s see how the above formula works:

The MID function will extract a sub-string from a text string at the position that you specify. It contains three arguments in MID function.

The first argument is the returned results of the first CELL function. The full workbook name and its path will be returned from the below CELL function.

=CELL("filename",B1)

Get the Current Workbook name1

The first FIND function will locate the position of the first bracket along with the workbook name. It is the second arguments as the MID function.

=FIND("[",CELL("filename",B1))

Get the Current Workbook name2

The second FIND function will return the position of the second bracket right of the workbook name.

=FIND("]",CELL("filename",B1))

Get the Current Workbook name3

The third argument of the MID function is the length of the substring that you want to extract. In other words, you need to get the length of the workbook name. Using the below formula:

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

Get the Current Workbook name3

Last, we can run the following excel formula to get the current workbook name:

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

Get the Current Workbook name5

Related Formulas

Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

 

Excel Cell Function

This post will guide you how to use Excel CELL function with syntax and examples in Microsoft excel.

Description

The Excel CELL function returns information about the formatting, location, size, or contents of a cell.

The CELL function is a build-in function in Microsoft Excel and it is categorized as an Information Function.

The CELL function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the CELL function is as below:

= CELL (info_type,[reference])

Where the CELL function arguments are:
Info_type -This is a required argument. The type of information that you want to get. The following types can be used.

From Microsoft site:

info_type Returns
“address” Reference of the first cell in reference, as text.
“col” Column number of the cell in reference.
“color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.
“contents” Value of the upper-left cell in reference; not a formula.
“filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.
“format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.
“parentheses” The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.
“prefix” Text value corresponding to the “label prefix” of the cell. Returns single quotation mark (‘) if the cell contains left-aligned text, double quotation mark (“) if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text (“”) if the cell contains anything else.

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.
“protect” The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.
“row” Row number of the cell in reference.
“type” Text value corresponding to the type of data in the cell. Returns “b” for blank if the cell is empty, “l” for label if the cell contains a text constant, and “v” for value if the cell contains anything else.
“width” Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

NOTE: This value is not supported in Excel Online, Excel Mobile, and Excel Starter.

Example

The below examples will show you how to use Excel CELL Function to retrieve information about a cell.

#1 =CELL(“row”,B1)

excel cell function example1

Note: the above excel formula will return the row number of Cell B1.

#2 =CELL(“contents”,A1)

excel cell function example2

Note: The above excel formula will return the content of cell A1.

#3 =CELL(“width”,B1)

excel cell function example3

Note: the above excel formula will return the column width of the cell B1.

More CELL Function Examples

  • Quickly Get Sheet Name
    If you want to quickly get current worksheet name only, then inert it into one cell. You can use a formula based on the MID function in combination with the FIND function and the Cell function……
  • Insert The File Path and Filename into Cell
    If you want to insert a file path and filename into a cell in your current worksheet, you can use the CELL function to create a formual……..