I believe you cannot do this with non-VBA solutions.
Conditional formatting itself will not set any alignment formatting so one must work with the "number" formatting tools.
Unfortunately, one cannot set the middle, so to speak, of text, just the beginning or end. Beginning won't help as it would push your text over to the right.
The end doesn't help either since you have multi-line data. So the end is ONLY the end of the last internal line. Not the ends of the other internal lines. All of them would be centered and only the last internal line would be aligned to the left. Even using some other character than Alt-255 would do the same.
I do experience the "#####" type problem you describe. Alt-255's blank works nicely for me, except the problem with it only affecting the last internal line. But longer text than I experimented with at first showed fine. Using your phrases shown, it did do the "#####" thing. However, widening the column solves that. But...
- You may not be able to do that at all.
- Text that fit in a 13.44 size column when not particularly formatted required a 26.00 size column when formatted so, with the Alt-255 character as shown. You might not be able to widen that much even if you can widen some.
And then the Alt-255 character stopped helping. The original text I used, in which it worked, stopped working when I began adjusting column width. Last line centered, no matter what. Copy the cell and paste normally to a default size column and it worked again. But re-sizing worked there. I realized that the left edge never really did go "left-justified" but rather more of a "left-indented justified" appearance. So, not hard up to the left, but a bit over, some whitespace showing on the left.
And the formatting had to be adding "X-many" spaces, Excel's internal decision about how many to add, no clue as to how it chose how many, but then THAT string was being centered, not left-justified, hence the whitespace to the left. Widening the column a lot showed basic centering still occurred. Kind of like smallish numbers seem to have a lot of significant patterns, but they break completely if you make the numbers larger.
So the formatting only appeared to be making the string left-justified when the string was short. When having to widen the column to fit long enough text, like yours, the centering became apparent enough to let me guess what was going on. And wider yet proved it.
So that formatting string will not work in any case. It will be good enough for quite short strings, 6 characters for me, one an 'l" ("el"), but not for your text.
That basically shuts down even the one option that allowed a reasonably close appearance of your need.
VBA could do this for you. A button to click by a user, it goes through and if there is a single line, it centers the text, and if multi-line, it left-justifies it. (So edits to existing data get caught and justified appropriately.) It ought to be lightning fast, one thinks. But it would require operator action. There are ways to automate that, "On Click" family of actions, but there are a ton of pitfalls so...
But "sheet-side" techniques? I believe you cannot get what you want, except for very short data displayed and even then, only "close enough for government work."
("Short data displayed" because even putting just a date and having the formatting added strings before the numbers triggers this exactly as when the text is literally entered rather than put there by Excel. Excel is working with the resulting display string, not the data "physically" there.)
