TEXT function with unusual results

[Beans]

When trying to convert numbers to text using the TEXT function SOME values
are putting in additional numbers. The formula I'm using is TEXT(A1,A1). The
problem replicates. See below. Does anyone know what is happening? Thanks.

735 735 735 735
736 736 736 736
805 88055 888805555 88888888055555555
814 814 814 814
828 828 828 828
830 83830 838383830 83838383838383830
832 832 832 832
836 836 836 836

 

[T. Valko]

TEXT(A1,A1)

The 2nd argument to the TEXT function must be a text representation of some
format. In your case that would be some number format. A1 is not a
legitimate format.

If all you're wanting to do is convert the numeric numbers to text numbers:

=A1&""

 

[Tom Hutchins]

The second argument of the TEXT function is the format to which the number
(first argument) should be converted. You are giving a cell value as a format
for this argument. It looks like Excel ignores it unless there is a zero in
the number you are using as a format. Then it seems to be repeating
everything before and after the zero. Try this TEXT formula instead:

=TEXT(A1,"0")

Hope this helps,

Hutch

 

[Ron Rosenfeld]

When trying to convert numbers to text using the TEXT function SOME values
are putting in additional numbers. The formula I'm using is TEXT(A1,A1). The
problem replicates. See below. Does anyone know what is happening? Thanks.

735 735 735 735
736 736 736 736
805 88055 888805555 88888888055555555
814 814 814 814
828 828 828 828
830 83830 838383830 83838383838383830
832 832 832 832
836 836 836 836

The second argument of the TEXT function is the number format.

In the context of your numbers, the only valid format for a number is the "0".
The other values are being interpreted as prefixes or suffixes to the number.

So, if you have a "0", as in your third row:

805: base number
Col2 format is equivalent to 805 or \80\5; so you see an "8", then the number
which is 805, then a "5".

Col 3 is now using 88055 for the format code, with a number being 88055
so this becomes the string "88" followed by the number 88055 followed by the
string "55"

You would see the same thing if you custom formatted the cell containing 805
with 805 (Format/Cells/Number/Custom Type: 805)

Even more interesting, try a formula of =TEXT(A1,A2). Instead of seeing 735,
you would see 736.

To convert numbers to text, using the TEXT function, use the formula

=TEXT(A1,"0")




--ron