Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CSV File - Leading Zeros | Excel Discussion (Misc queries) | |||
Is it possible to show leading zeros for "number" cells? | Excel Discussion (Misc queries) | |||
How do I force leading zeros in an Excel cell? | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) | |||
Leading zeros | Excel Discussion (Misc queries) |