Value is simpler indeed - but as you say, only strips if you're trying to clean zeros. Substitute is needed if you want to clean non-zeros, which as you say, you asked in the first post looking for 1s.
EDIT: BTW to keep the format when using Value, use:
=TEXT(VALUE(A1), "#")
Careful. Looks like there's some rounding going on somewhere for me.
=TEXT(VALUE(A1), "#") on
00000232322200200230200203020032300011111000000
gives:
232322200200230000000000000000000000000000
doesn't seem to work with either 0 or "#". It's not recognising a non zero after any zeros in the sequence. So 00011100020 which would become 11100020 then becomes 11100000.
I possibly should have put the whole reasoning behind the original query up but it would have been a bugger to type out and as it was a slight tweak I thought taking the bit that needed tweaking was sensible. Sorry if that was not the case.
Anyway, for now Stoner I'll be using the original solution....which is still much better than VBA 
Looks like it's magically storing it in Scientific format but displaying it in 'normal' format, hence the missing information.
Quite interesting, must be a way around this but what are the chances that it will end up far more complicated than Stoners solution! 
good point re the "#" thing - bluddy floating point calcs
So - in the end you still have to use Substitute
Excel can't handle number bigger than 15 digits. What happens with the VALUE function is that the text is changed to a number, which then breaches this limit, so all digits after the 15th are changed to zero.
So, VALUE is no good.
you see what you need is to use some other form of counting system than binary. like, maybe decimal or something - someone might even have invented it already though 
This topic has been closed to new replies.
Advertisement
Advertisement
Advertisement
Advertisement
Advertisement
