When I use ="""" & TEXT("1468", "# ###") & """", "1 468" is returned. Perfect.
However, once 4 digits become 3 digits, I get an empty space at the beginning. " 468". Not perfect.
What text format can I use to avoid that empty space?
1 Answer
Just wrap the inner portion in TRIM( ):
="""" & TRIM(TEXT("468", "# ###")) & """"
TRIM removes superfluous leading, trailing and interposed space characters.
10 Comments
Wow. Perfect. Thank you so much! Is it possible to do something similar with the custom number format? Tried here: i.imgur.com/ixgwSmG.png
Erik Tyler
Try this custom number format:
[<1000]\"0\";[>999]\"# ##0\";0;@Dang. You are a genius. Thank you for that, Erik. It works in the sense that the values are finally properly centered (no longer having empty spaces in front that make everything look weird). Since I do not fully understand the format you have shared here, may I ask one more question? I used quotation marks to easily illustrate the empty space. I do not intend to use these otherwise. How should I adjust your format to exclude quotation marks, and what do I have to change if there are really many digits (like 10 digits instead of 4 digits)?
Erik Tyler
If you explain fully what you are trying to do, it will be a more efficient use of our time. For now, to get rid of the quotes, eliminate the four instances of
\" from the custom format string.That worked better. Appreciate it. i.imgur.com/qtakyRP.png What I am trying to fully achieve is apply the black-line format without empty spaces. That would be like the red-line format (your format), but it does not work properly for larger digits. Do you understand what I mean?
|