If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
add zeros to front of text string
Here ya go
=+CHOOSE(10-LEN(D7),"0","00","000","0000","00000","000000")&D7 What this does is decides how many 0s to put in front of the number based on how long it is, then joins it with the original "number" (because it's text). "Napkintoe" wrote in message ... I am importing data that always has 10 places. (ISBN numbers) Some of the ISBN's start with either 1 or 2 zero's I want to tell excel to always make the srting 10 places long by adding zero's if necessary. 28404203 should be 0028404203 658009729 should be 0658009729 3765034291 should be left as it is. I thought the =text function was the answer but I am stymied. Thanks for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
add zeros to front of text string
How about
=RIGHT("0000000000"&A1,10) ? Rgds, Andy |
#3
|
|||
|
|||
add zeros to front of text string
actually the number doesn't have to be text, but the result will be text. as
a side note, presumably the ISBNs are stored as strings of length 10 in the file you are importing from, right? If so you should be able to import them in that format instead of importing as numbers, then converting to text strings as the formula will do. also in case you didn't notice, that formula will work if the value is as small as 4 digits. if you want to go smalller just continue the pattern. "Dave R." wrote in message ... Here ya go =+CHOOSE(10-LEN(D7),"0","00","000","0000","00000","000000")&D7 What this does is decides how many 0s to put in front of the number based on how long it is, then joins it with the original "number" (because it's text). "Napkintoe" wrote in message ... I am importing data that always has 10 places. (ISBN numbers) Some of the ISBN's start with either 1 or 2 zero's I want to tell excel to always make the srting 10 places long by adding zero's if necessary. 28404203 should be 0028404203 658009729 should be 0658009729 3765034291 should be left as it is. I thought the =text function was the answer but I am stymied. Thanks for your help ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
add zeros to front of text string
Clean!
"Andy Brown" wrote in message ... How about =RIGHT("0000000000"&A1,10) ? Rgds, Andy |
#5
|
|||
|
|||
add zeros to front of text string
Thanks for the great suggestions!
Here is what I ended up using and it works so far Highlight Column Format cells: Number :Custom Choose the "0" Enter 10 Zeros and Wallah! I hope I'm not missing something but this seems to be the easiest way to handle this. Thanks again ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#6
|
|||
|
|||
add zeros to front of text string
Highlight Column
Format cells: Number :Custom Choose the "0" Enter 10 Zeros and Wallah! Sure that'll *look* right, however it doesn't actually change the string length, which is what I thought you wanted. But hey ... Best rgds, Andy |
Thread Tools | |
Display Modes | |
|
|