Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
haitch2
 
Posts: n/a
Default 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

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

  #2   Report Post  
DennisSunga
 
Posts: n/a
Default


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

  #3   Report Post  
haitch2
 
Posts: n/a
Default


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

  #4   Report Post  
DennisSunga
 
Posts: n/a
Default


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

  #5   Report Post  
haitch2
 
Posts: n/a
Default


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.


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



  #6   Report Post  
Sloth
 
Posts: n/a
Default

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


  #7   Report Post  
Sloth
 
Posts: n/a
Default

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


  #8   Report Post  
haitch2
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CSV File - Leading Zeros Floridagal Excel Discussion (Misc queries) 2 August 6th 05 12:34 AM
Is it possible to show leading zeros for "number" cells? Columbus Excel User Excel Discussion (Misc queries) 1 July 21st 05 06:19 PM
How do I force leading zeros in an Excel cell? EricKei Excel Discussion (Misc queries) 2 June 15th 05 08:28 PM
Leading zeros Paul Excel Discussion (Misc queries) 4 June 12th 05 04:04 AM
Leading zeros JC Excel Discussion (Misc queries) 9 February 1st 05 03:33 PM


All times are GMT +1. The time now is 12:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"