Extract data from formula not from the results of the formula

[excel?]

I have a formula that pulls text from another file. I want to pull a number from the Formula but instead I get the section from the text the formula is returning. Any suggestions on how to get the number not the text?

Formula (in Cell C14):
='[2021 Data Sheet.xlsm]30246'!$C$6

Data formula returns:
Red Small Apples -Red Delicious

I want to pull the number 30246 from the formula. I tried to use the MID formula, but it returns the Text data not the number form the formula.
=MID(C14,23,5) returns Delic not 30246.

I would appreciate any suggestions you might have. Thank you

 

[Joe4]

By default, formulas run against the value in the cells, not the text of the formula.
However, there is a FORMULATEXT function that will allow you to do this, provided that you are using a new enough version of Excel (see here: FORMULATEXT function)

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have access to this, then your formula would just be:

=MID(FORMULATEXT(C14),23,5)

 

[excel?]

By default, formulas run against the value in the cells, not the text of the formula.
However, there is a FORMULATEXT function that will allow you to do this, provided that you are using a new enough version of Excel (see here: FORMULATEXT function)

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have access to this, then your formula would just be:

=MID(FORMULATEXT(C14),23,5)

Thank you so very much. That is exactly what I needed. And thanks for the version feedback.

 

[Joe4]

Thank you so very much. That is exactly what I needed. And thanks for the version feedback.

You are welcome.
Please update your Account details, when you have the chance.