"#"##, or "#"@ - what works with both?

[StargateFan]

Oh boy, iif there's a problem, it might take years but eventually, I
find it! <g> I ran into the problem on Friday where I had a custom
cell format that, for once, didn't always work because this sheet has
number + letter combinations.

"#"## results in:
#1
#2
#3
1a
2a
3a, 3b

Where "#"@ gives me:
1
2
3
#1a
#2a
#3a, 3b

Is there any way to get it consistently, like so:
#1
#2
#3
#1a
#2a
#3a, 3b
with the user just entering the number or number + letter alone?

Thank you! D

 

[Rick Rothstein]

I'm assuming you don't have negative values (if you do, show us where you
want the minus sign to go). Try using this Custom Cell Format...

\##;;\#0;\#@

For your 3a, 3b example, the above custom format will only place the # sign
in front of the whole entry, not each individual part (that would take VB
code and would physically modify the entries so you would lose the original
values in the cells (they would physically have the # signs inserted into
them).

 

[StargateFan]

I'm assuming you don't have negative values (if you do, show us where you
want the minus sign to go). Try using this Custom Cell Format...

\##;;\#0;\#@

Oh, wow, that's works great! Thank you.

No, didn't think of that but there are no negative values this time
around. This case hadn't come up before I guess as there's only one
voucher per travel expense claim item (which is what this spreadsheet
is about). But I had to break this particular expense down so that I
ended up with a couple of number+letter entries. My usual custom
format didn't work on those. This format here does so it's become my
new standard <g>.

Thanks! I'll fix the form on Monday.

Cheers. D