find the

Return Sheet Name into a Cell - Excel Formula

hello welcome to this video tutorial

from computer gargle comm in this video

we are going to look at writing an excel

formula that will return the name of a

sheet into a cell now one of the main

functions to help us do this will be the

sale function this is a brilliant

function in Excel because it allows you

to turn information about a cell such as

its color it's protected status it's

column number it's sort of information

and one of the bits of information you

can return using the cell function is

two cells a file name and that's useful

for us because that will include the

sheet name so let us start there in cell

a1 here or obviously you can write this

in any cell I'm going to write this cell

function and here it is returns

information about the formatting

location content so on and so forth and

I'm going to ask it to return the file

name and I'm going to ask it to return

the file name of cell b1 now you can

reference any cell at this point there's

nothing special about b1 any cell will

do because my ultimate goal is to find

the sheets name so any cell any sheet or

the sheet that you want will do the job

as I run that I can see it returns to

full path so I've got a sheet name on

the in here which is Chicago for this

one but I've also got the name of the

workbook I've got the they kind of

folder structure like this in the

directory I'm not interested in that

stuff but we're getting it what we're

now going to do is add to that formula

the mid and find functions I'm going to

use the mid function so that I can

extract part of that string the part I

want being a sheet name I'm going to use

to find function to find the sheet name

within the string and I'm going to use


closing square bracket as the kind of

indicator as to where the sheet is I

don't know how long the filename and it

will be said of sheet always appears in

a different position but if I can find

that closing square bracket the sheet

name always follows it so and we

double-click on that sale and at the

start of that formula and we do it

within the sale and then to add the mid

function I'm going to ask the mid

function to look at that piece of text

that the cell function is returning for

me it asks for starting number where is

the middle that you asked for that is

where the find function is going to come

in I'm going to ask this find function

to find the closing square bracket have

to wrap that in the inverted commas

comma where where are we looking for a

closing square bracket lets me copy and

paste that cell function because

wherever string that cell functions

bring back that file name as they call

it it begins to ask if I want to start

in a particular position to look for


I don't going to close that off so I'm

back in the mid function now and plus

one so you can find that closing square

bracket in what the cell function tells

you and then plus one so you go to the

next character the thing after the

closing square bracket comma and now how

many characters from that point I'm

going to type 32 which is excessive for

things like Chicago that is seven

letters and you can see other sheets at

the bottom like Miami is five and New

York would be eight characters but in a

realistic situation I don't know the

name of this the sheet I believe sheet

names can only be up to 31 characters

will hit earlier about some basically

type in a number excessive for my needs

I want to close up bracket down and

press ENTER and we have our sheet name

and if I was to copy and paste that

formulas to the other sheets just to

show it doing its thing they will return

a required sheet name and a great

benefit of using the formula to return

that sheet name is if somebody was to go

to the Miami sheet and maybe rename it

London then F formula instantly updates

with the new sheet name so you have that

dynamic that automated kind of situation

going on I hope you find this video

tutorial useful please check out some of

the other videos on our youtube channel

and at computer gaga