# Function to put worksheet name in cell returns text, need number

P

#### Pennyc

I found the following great formula on this website to put my worksheet name
into a cell on that worksheet. (this is in Cell L2 of each worksheet)

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

However, I need to format that result as a number because I'm doing a
Vlookup using that value as the reference.

=VLOOKUP(\$L\$2,'PO Log'!\$B\$4:\$BW\$503,4,FALSE)

The range 'PO Log'!\$B\$4:\$BW\$503 contains formulas that add 1 to the value in
the cell above to create a list (which will have a different beginning number
depending on the user), so I really can't change these values to a text
format and keep the same functionality of the workbook.

I've tried changing FALSE to TRUE, but that won't work. I also tried to
force the result of the MID fucntion into a numeric format, but no success
there either.

=TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"######0")

Even if I copy & paste Values where the MID function is, I cannot reformat
that value as a number.

Is there anyway to ensure that the MID function returns a numeric value?

I should mention that I created and named each of these worksheets with a
macro (from this site) that uses the numbers in the reference range on the
'PO Log' page to create the sheet name in the first place.

I'm desperate as I am scheduled to roll out this worksheet to a group of my
peers tomorrow. Can anyone help?

J

#### Jacob Skaria

Try
=--MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

If this post helps click Yes

J

#### Jim Thomlinson

Mid returns a string. Try this to coerce the string to a number...

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

L

#### Luke M

You could try encasing your formula within either the VALUE or N functions.
(See XL help file)

P

#### Pete_UK

Another way is to change your lookup formula:

=VLOOKUP(\$L\$2*1,'PO Log'!\$B\$4:\$BW\$503,4,FALSE)

The *1 will force the value of L2 to a number, so you could put it
here or add it to the formula in L2.

Hope this helps.

Pete

P

#### Pennyc

This did the trick. Thanks so much!

Jim Thomlinson said:
Mid returns a string. Try this to coerce the string to a number...

=value(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))
--
HTH...

Jim Thomlinson

Pennyc said:
I found the following great formula on this website to put my worksheet name
into a cell on that worksheet. (this is in Cell L2 of each worksheet)

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

However, I need to format that result as a number because I'm doing a
Vlookup using that value as the reference.

=VLOOKUP(\$L\$2,'PO Log'!\$B\$4:\$BW\$503,4,FALSE)

The range 'PO Log'!\$B\$4:\$BW\$503 contains formulas that add 1 to the value in
the cell above to create a list (which will have a different beginning number
depending on the user), so I really can't change these values to a text
format and keep the same functionality of the workbook.

I've tried changing FALSE to TRUE, but that won't work. I also tried to
force the result of the MID fucntion into a numeric format, but no success
there either.

=TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"######0")

Even if I copy & paste Values where the MID function is, I cannot reformat
that value as a number.

Is there anyway to ensure that the MID function returns a numeric value?

I should mention that I created and named each of these worksheets with a
macro (from this site) that uses the numbers in the reference range on the
'PO Log' page to create the sheet name in the first place.

I'm desperate as I am scheduled to roll out this worksheet to a group of my
peers tomorrow. Can anyone help?
{"ft_lang_label":"__label__en","ft_lang_prob":0.8868671,"math_prob":0.8258573,"size":2687,"snap":"2022-27-2022-33","text_gpt3_token_len":712,"char_repetition_ratio":0.1155423,"word_repetition_ratio":0.9543379,"special_character_ratio":0.2631187,"punctuation_ratio":0.115384616,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.98013985,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2022-08-17T10:49:59Z\",\"WARC-Record-ID\":\"<urn:uuid:5f8a371b-5f85-4899-bc7a-a28dfd7e992b>\",\"Content-Length\":\"72468\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:cc0411d3-4a45-4d28-ab53-00ac3fed8cb7>\",\"WARC-Concurrent-To\":\"<urn:uuid:ed178f1b-eeeb-4ca4-83b3-2ab58cd70e29>\",\"WARC-IP-Address\":\"104.21.6.187\",\"WARC-Target-URI\":\"https://www.pcreview.co.uk/threads/function-to-put-worksheet-name-in-cell-returns-text-need-number.3891969/\",\"WARC-Payload-Digest\":\"sha1:2P7Q7XE32KH76TYEXBBHV5CWF5BR652B\",\"WARC-Block-Digest\":\"sha1:YSU4LUHWLD7QBWO5OPUJGKTGGHGRWEWO\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2022/CC-MAIN-2022-33/CC-MAIN-2022-33_segments_1659882572898.29_warc_CC-MAIN-20220817092402-20220817122402-00418.warc.gz\"}"}