custom format for percents

[craigexcel]

I’m trying to come up with a custom cell format that will display percentages (only the numbers), separated by the ‘pipe’ character (above the backslash on the keyboard).

Initially, I decided on “00 | 00”, which works fine for nearly all combinations. But of course, some cells will need to be “0 | 100”, or, “100 | 0”, which will then display as “01 | 00”, or, “10 | 00”, which doesn’t reflect the values correctly.

I also tried entering the data as “0,100” which works, but that display seems like it could cause some potential confusion. (At this point, just knowing what the percentages are is the goal. I don’t think the numbers represented by each cell’s contents will need to be used. If so, I can extract by formula.)

Can a custom format be devised that accounts for these too? A custom format is as far as I want to go with this, and can’t use a helper column either. I’d appreciate any suggestions you may have.

[note: a question for work, so those tech details are Excel 2016, running on Windows 10 Enterprise, which is different from my profile.]

 

[craigexcel]

And obviously, I can manually enter '<percent> | <percent>', and that will work. But I'm hoping to avoid having to actually type the pipe character, and the 2 values will still display correctly.
Further, I could always format the cells as "0000", but think that could potentially be confusing too.

 

[EXCEL MAX]

Try this cell format... 00"%" "|" 00"%"

 

[craigexcel]

Try this cell format... 00"%" "|" 00"%"

Thanks for your reply, EXCEL MAX. I tried that format, entered "0100", and the displayed 'value' is
01% | 00%

While I could work with the % signs, I need for the "1", in this case, to be to the right of the pipe character.

 

[EXCEL MAX]

If you trying to show numbers with three digits you need to add three zeros to format from both sides...
000"%" "|" 000"%" and enter number with six digits,
otherwise computer will be confused asking himself would you like to show number with three digits
from left or right side of the pipe.

 

[Rick Rothstein]

What is the actual number in your cells for the expected 0|100 and the 100|0 cases?

 

[Tetra201]

Maybe
[=100]0 | 000;[=1000]000 | 0;00 | 00

 

[craigexcel]

Rick Rothstein -- I think the "actual" number, in this case, is a misnomer. What I'm looking to do is enter, into a cell, what is essentially an allocation notation, the theoretical sum (of the 2 'values') is to be 100%. A cell's 'value' could be "80 | 20", "15 | 85", "0 | 100", etc.

Tetra201 -- though I haven't quite figured out that custom format yet, I did try that format, and it does exactly what I'm looking for. Many thanks!!!

 

[Tetra201]

Glad to have helped.