Spliting a number with leading zeros

[haitch2]

Hi, im new to this forum so hello.

Ive been searching around the net for a solution but of yet have no
found one, so hoping that sombody will be able to give me a hand. (i
making a binary to decimal converter for my work at college, i no ho
to do the actual code to convert it im just trying to make it neat)

Im trying to split a number with leading zeros. Im using the mi
function to split out the number but for some reson it wont see th
leading zeros

ie spliting 00011110
im using =MID(b4,2,1) which should bring up 0 but its counting th
characters from the first 1.

ive sort of found a temorary solution by converting it to text, but th
imputer has to remeber to put leading zeros on so it makes it
characters.

is there any way to make excel add the zeros on (in a text field) t
make it up to 8 characters what ever the imputer puts in.

Any help is much appreciated as im not very good at excell (know mor
about access, thats were i got the mid function) so please dont be t
complicated

Thanks in advanced

Hele

 

[DennisSunga]

Try this. I'm not sure this is what you want but it displays
characters with leading zeroes all the time.

Format the cell or cells as follows;
Format, Cell, Number
and choose CUSTOM.

IN the box, enter 0000000#

then ok.
When you enter 45, it should display 00000045. If you enter 7654321
it would be displayed as 07654321

 

[haitch2]

Thanks for the promt repley.

Unfortunatly ive done this with the number that i cant split properl
(ignors the zeros total and splits at the first 1). Is there away yo
can do this with text instead of numbers.


Thank

 

[DennisSunga]

There are 2 ways and again I'm not sure this is what you want.
Method 1
On cell C5, they the following formula.
=+IF(ABS(B5<10),"0000000"&B5,IF(ABS(B5)<100,"000000"&B5,IF(ABS(B5)<1000,"00000"&B5,IF(ABS(B5)<10000,"0000"&B5,IF(ABS(B5)<100000,"000"&B5,IF(ABS(B5)<1000000,"00"&B5,IF(ABS(B5)<10000000,"0"&B5,B5)))))))

ON Cell B5 is where you enter the number. On Cell C5, it displays th
number as text filling in the required number of zeros.

Method 2
Use a vlookup as follows
Create a table array in say, F1 to G8. Enter the following numbers o
each cell.
F1=0 G1='0000000
F2=10 G2='000000
F3=100 G3='00000
F4=1000 G4='0000
F5=10000 G5='000
F6=100000 G6='00
F7=1000000 G7='0
F8=10000000 G8=(blank)

the zeroes in column G are entered as text.
In Cell C1 Enter the following formula
=+VLOOKUP(B1,$F$1:$G$8,2)&B1

Enter your values in B1. Cell C1 displays the value with leading 0'
as text.
The $, I assume you know, is so that you can copy that formula dow
without changing the table it references.
The concept is it looks up the number you typed and compares it to th
table, returns the correct number of leading 0's + your number.

I hope this helps

 

[haitch2]

Your a realy star, the first option you gave me work just how i wanted.


I started going along the other route you said (method 2) but i just
didnt have the knowlege to see it though, looking at how you have done
it, it seem far more complicated then i had first thought. The logic
was there just not the know how

Thanks alot for all your help.

 

[Sloth]

Use the text function when pulling numbers if you want to keep the leading
zeros.

For isntance...
A1: 00011101
B1: =LEFT(TEXT(A1,"00000000"),4)
B1 outputs: 0001

To use your example...
A1: 00011110
B1: =MID(TEXT(A1,"00000000"),2,1)
B1 Outputs: 0

The numbers will have to be formatted with a custom format. I can't help
you if you still want it formatted like text.
Ie. Custom Format: 00000000

 

[Sloth]

One small correction. . .

the format can be anything, including text. It really doesn't need any
leading zeros to begin with (for example using the left function given below
for a value of 123456 in cell A1 would yield 0012). If you don't mind it
being a number, the "00000000" custom number format is the best option in my
opinion.

As far as your original question about adding leading zeros to text, I can't
help you. Sorry. I assumed the TEXT function would fix the problem you
first had.

 

[haitch2]

Thankyou

This is a much simpler solution and worked wonders with no
limitations.

The other method has solved enother problem i had, so that was great as
well

Thankyou all for all you help. Its been much appreciated.

Helen