Spliting a number with leading zeros
 

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 :rolleyes:

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

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.


--
DennisSunga
------------------------------------------------------------------------
DennisSunga's Profile: http://www.excelforum.com/member.php...o&userid=27514
View this thread: http://www.excelforum.com/showthread...hreadid=471900

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


--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=471900

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,"00000 0"&B5,IF(ABS(B5)<1000,"00000"&B5,IF(ABS(B5)<10000, "0000"&B5,IF(ABS(B5)<100000,"000"&B5,IF(ABS(B5)<10 00000,"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.


--
DennisSunga
------------------------------------------------------------------------
DennisSunga's Profile: http://www.excelforum.com/member.php...o&userid=27514
View this thread: http://www.excelforum.com/showthread...hreadid=471900

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 :rolleyes:

Thanks alot for all your help.


--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=471900

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 :rolleyes:

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

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 :rolleyes:

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

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


--
haitch2
------------------------------------------------------------------------
haitch2's Profile: http://www.excelforum.com/member.php...o&userid=27677
View this thread: http://www.excelforum.com/showthread...hreadid=471900