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 not found one, so hoping that sombody will be able to give me a hand. (im making a binary to decimal converter for my work at college, i no how 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 mid function to split out the number but for some reson it wont see the leading zeros

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

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

is there any way to make excel add the zeros on (in a text field) to 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 more about access, thats were i got the mid function) so please dont be to complicated

Thanks in advanced

Helen

[DennisSunga]

Try this. I'm not sure this is what you want but it displays 8 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 properly (ignors the zeros total and splits at the first 1). Is there away you can do this with text instead of numbers.


Thanks

[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 the 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 on 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's as text.
The $, I assume you know, is so that you can copy that formula down without changing the table it references.
The concept is it looks up the number you typed and compares it to the 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

"haitch2" wrote:

>
> Hi, im new to this forum so hello.
>
> Ive been searching around the net for a solution but of yet have not
> found one, so hoping that sombody will be able to give me a hand. (im
> making a binary to decimal converter for my work at college, i no how
> 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 mid
> function to split out the number but for some reson it wont see the
> leading zeros
>
> ie spliting 00011110
> im using =MID(b4,2,1) which should bring up 0 but its counting the
> characters from the first 1.
>
> ive sort of found a temorary solution by converting it to text, but the
> imputer has to remeber to put leading zeros on so it makes it 8
> characters.
>
> is there any way to make excel add the zeros on (in a text field) to
> 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 more
> about access, thats were i got the mid function) so please dont be to
> complicated
>
> Thanks in advanced
>
> Helen
>
>
> --
> haitch2
> ------------------------------------------------------------------------
> haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
> View this thread: http://www.excelforum.com/showthread...hreadid=471900
>
>

[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.

"Sloth" wrote:

> 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
>
> "haitch2" wrote:
>
> >
> > Hi, im new to this forum so hello.
> >
> > Ive been searching around the net for a solution but of yet have not
> > found one, so hoping that sombody will be able to give me a hand. (im
> > making a binary to decimal converter for my work at college, i no how
> > 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 mid
> > function to split out the number but for some reson it wont see the
> > leading zeros
> >
> > ie spliting 00011110
> > im using =MID(b4,2,1) which should bring up 0 but its counting the
> > characters from the first 1.
> >
> > ive sort of found a temorary solution by converting it to text, but the
> > imputer has to remeber to put leading zeros on so it makes it 8
> > characters.
> >
> > is there any way to make excel add the zeros on (in a text field) to
> > 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 more
> > about access, thats were i got the mid function) so please dont be to
> > complicated
> >
> > Thanks in advanced
> >
> > Helen
> >
> >
> > --
> > haitch2
> > ------------------------------------------------------------------------
> > haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
> > View this thread: http://www.excelforum.com/showthread...hreadid=471900
> >
> >

[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