1

Let's say if the function called "LABEL".

Then in cell A1 I would enter =LABEL(4,"Four Apples") and it would render as:

Four Apples

But if I performed a calculation, say =A1+2 I would get 6, not #VALUE!

There are a number of applications where I would find this very useful, I know something similar can be achieved using custom number formats but this wouldn't solve my problem in all the instances.

Thanks in advance,

Improve this question
4
  • 1
    You can construct a function like this, but it would require a VBA macro. Is a macro solution acceptable?
    – Gary's Student
    Jun 9, 2015 at 11:04
  • I was hoping there was something in vanilla excel that I'd missed, but if that's the only option and you don't mind helping me to create a custom function, then I would appreciate it :) But I know it is a bit cheeky to just ask for code...
    – Some_Guy
    Jun 9, 2015 at 11:34
  • possible duplicate of Excel - display text different to the actual value?
    – Raystafarian
    Jun 9, 2015 at 12:01
  • It looks like that question is in fact the same as mind (couldn't find it when I searched) However I did ask for a solution other than custom formats. Was hoping for a formula but yeah it does look like my choices are either formatting or VBA
    – Some_Guy
    Jun 9, 2015 at 13:30

2 Answers 2

Reset to default
2

You can force the format. For example, if you place any number in the cell and format the cell to:

"Four Apples";"Four Apples";"Four Apples";

The cell will display Four Apples but still retain the numeric value (as viewed in the Formula Bar)

enter image description here

Improve this answer
1
  • This is probably my best option, I did mention this in the original post but thanks anyway :)
    – Some_Guy
    Jun 9, 2015 at 13:26
0

Gary's Student already asked if you want to use VBa (which is how I'd rather do it) in a comment of your post which you didn't yet answer, however, you could possibly use this solution if you only want to use Worksheet Formula (I use the word solution loosely, hacking to heck is better)!

Now, this answer is based upon the information you've given. The only example you gave is "Four Apples"

Assume you have the following in Sheet1

    A                B                    
1  four apples     
2  six oranges     
3                    =sum(5 + A1)
4                    =sum(A2 / 2)

Where B3 and B4 are to show results, not the actual formula

The only way is with this hack. In worksheet 2, create a look up such as

       A                 B
1     one                1
2     two                2 
3     three              3

etc

Then, you can use the following

=SUM(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),Sheet2!A1:B6,2,FALSE)+10) 'Look up the four apples and add 5

=SUM(VLOOKUP(LEFT(A2,FIND(" ",A2)-1),Sheet2!A1:B6,2,FALSE)/2) 'Look up the six oranges and divide by 2
Improve this answer
1
  • Yeah I've come up with a few workarounds, unfortunately they all require an extra column with substitutions. Looks like it's either a macro or just time for me to design better spreadsheets that don't require this.
    – Some_Guy
    Jun 9, 2015 at 11:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .