2

What's the difference in using the Formatting drop-down and using =text() to format cells? Which one overrides the other or take priority? It seems I'm having some formatting conflict that is breaking my =unique() range and causing duplicates. Even though the rows looks the same, they are not de-duplicated.

enter image description here

enter image description here

2

in essence, =TEXT() is superior over "drop-down" internal formatting because of higher priority:

5

6

5

note: keep in mind that =TEXT() will convert the input into an actual chunk of text regardless of forcing numeric output with "0.0". to convert it back you need to use =VALUE()

note2: on 3rd image you can see that internal formating overrode function formula, however, this is a purely visual issue eg. in a sense we could say that internal formatting is superior in terms of visual outputs - otherwise not.

  • Thank you! I have always been wondering what I should be putting in the 2nd param of Text() to force things to become "plain text". Turns out whatever I use it'll still be plain text? I'm not quite sure if I understand the second part of Note2, do you mean that the drop-down only changes what is displayed, but not the actual value used for calculation? i.e. It can show 6decimal accuracy but uses only 2decimal to calculate if I used text("X", "0.00")? – Allen Sierra Jan 28 at 11:16
  • 1
    @AllenSierra yes, "whatever" you use the final output is text, however, that "whatever" needs to be a valid mask (like dd, mmm, yyyy, #, 0, hh, mm, ss or even @). on behalf of your Note2 question... yes and no. yes for picture 3 but no for picture 1 - on which internal formatting "crippled" the actual value – MARK MY ANSWER Jan 28 at 13:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.