=Text(number,"0")

[petpeswick]

I just "upgraded" to office 2016 and i was attempting to do some basic spreadsheet stuff. In previous versions if you had 56 in cell A1 and jumped into a text string formula =$a$1&"is a number" the result would be 56 is a number. Office 2016 is forcing me to use the text function. Is there a setting somewhere to resolve this? Can Excel no longer figure out what string =value(56) should look like?

Grrr,

Please advise,

[Syrkrasi]

In previous versions if you had 56 in cell A1 and jumped into a text string formula =$a$1&"is a number" the result would be 56 is a number.

Your example works in all versions; however, you could also use: =CONCATENATE(A1," is a number")

update: your formula will not work if the cell is format as TEXT. Change it to General or Number.

[petpeswick]

This problem is happening right now. It actually doesn't work with my freshly installed 2016 version. Does anyone know of a setting that enables or disables this feature? Of course it is better coding to use the text function, but this is not what we are used to.

[mehmetcik]

if A1 contains A56

in B1 =right(A1,2)*1 will return 56

in C1 =B1-4 will return 52

So instead of =Value(56) try =56*1

[sandy666]

it works
you can try: =A1&" is a number? "&ISNUMBER(A1)

[sandy666]

Colud you attach excel file with this problem?
or
=--TEXT(56,"0") and you will get value as number.

[petpeswick]

Perhaps I haven't adequately explained...
I am used to excel being able to convert a number to a text string just by using it in a text string formula as per the example in the original post. I have installed 2016 and am using it for the first time. I would like to have excel behave in the way I am used to when using numbers in text strings, but sadly it is not. My work around so far is to use the text formula which adds 10 extra characters to my equation each time i wish to reference a number. I submit that there must be a setting somewhere in the bowels of excel that has disabled this auto conversion of values to text used in this context. If anyone is aware of how to manipulate said setting or provide alternate explanation for this phenomenon, it would be greatly appreciated.

[sandy666]

or check your default settings for Normal style

style.jpg

[xlnitwit]

Hi,

Maybe you turned on one of the Lotus compatibility settings? What happens when you use the formula without TEXT involved?

[sandy666]

So you typing eg. 56 in A1 and this is a text but you want this as number?
Check format of A1 and if it is text format change to general or number
or I really don't understand where your problem is

[petpeswick]

Hi, so, Sandy666, the attachment tool doesn't seem to work.
xlnitwit, without using Text, I get #VALUE! result.
Cells with numbers are "general" or "number" format.

[xlnitwit]

Do you have any of the Lotus compatibility options checked for that sheet in the Advanced sections of Excel options? That's the only time I've ever seen & return a #VALUE error (other than concatenation of error cells)

[petpeswick]

xlnitwit, your the winner!!!
Transition formula evaluation was checked under lotus compatibility.

Thanks very much!

[sandy666]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.