FYI: Formula cells don't show consecutive spaces (" "&" ") unless in expanded view. But, why not?
TLDR:
Concatenated text shown in the formula field depicts consecutive spaces as a single space. The real, complete, result of the formula only shows up in the expanded cell view (or, as I learned, when you copy and paste out of the ‘abridged’ formula field)
Description of Issue/Discovery
This was going to be a question because I have been struggling to figure out how to get a CONCATENATE formula to allow me to include multiple consecutive spaces (" ") in the result.
For example:
- {Label-Lot}&" | “&IF({Delivered Amount}=BLANK(),” “&” “&” “&{Unit},{Delivered Amount}&” "&{Unit})
- {Label-Lot}&" | “&IF({Delivered Amount}=BLANK(),” “&{Unit},{Delivered Amount}&” "&{Unit})
When the {Delivered Amount} field has a value, I get the result that I want,
eg: Lot ALOF19 | 16 oz
But, when the {Delivered Amount} field is blank, both of these formulas (and many other attempted workarounds) give a result that shows only 1 space instead of the 3 that I need.
Like this: Lot EUPE20 | oz
(FWIW, I need to leave enough room to write the weight by hand on the label if it isn’t recorded in the airtable)
You can see both types of formula results in the {Label-Lot+Wt} field on the far right side of the screenshot I’ve attached.
No matter what I tried, the calculated results shown in the formula column will never show more than one space–so I finally gave up, searched the community forums, and started writing a new topic question to get help…
BUT, when I copied an entry from my formula field column that looks like this: Lot EUPE20 | oz
and pasted it here for my question, the 3 consecutive spaces magically show up just like I want them!:
Lot EUPE20 | oz
Edit: Ha! Is it a markdown thing? The bold text directly above looks correct (showing all 3 consecutive spaces before “oz”) in my editing window, but then posts to the forum with only 1 space showing… :grimacing: :thinking:
I’ve since noticed that the complete text can actually be found in Airtable if you expand an individual cell, even though it only it will never show consecutive spaces in the full field column view.
I’m relieved to have answered my own question, kind of–but, I don’t understand WHY it is structured this way. In my circumstance, it makes it much harder to check that my formulas are producing the exact text results that I need because what you see is not what you get. Is there some benefit to this behavior that I’m missing, or a computational snag triggered by multiple consecutive spaces in a formula?
Thanks!
