
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
I need to create a list of numbers and some of the numbers have leading zeroes. I know that I can create a number stored as text by using the ('). But I need the 'number' not the 'text.' Any suggestions?
Similar Excel Video Tutorials
Add Leading Zeroes w Formula
 See how to add leading zeros to a text string with an array formula. See the functions IF, LEN, MAX and REPT in one array formula. The LEN functi ...
Zip Code, VLOOKUP & TEXT functions
 See how to use the TEXT and VLOOKUP functions to retrieve Zip Codes with leading zeroes. See how to use Custom Number Formatting (format). ...
AVERAGE without Zeroes
 See Mr Excel and excelisfun create formulas that will calculate the AVERAGE excluding zeros (0) in the original data set. See the functions SUM, COUNT ...
Similar Topics
If I have several thousand rows of text strings which all leading zeroes and where the number of total characters in the string is consistent, but which have differing amounts of leading zeroes, is there a way to automatically strip zeroes. In other words is there a function which will keep removing zeroes until it comes to a nonzero character?
If not does anyone have alternative ideas?
Thanks!
Tom
I have a situation where I have employee numbers ranging from 1 to 5 digits. I need to add leading zeroes to make the field nine digits total. (I can do this by using Format/Custom/and entering 9 zeroes.) At the same time, I need the field to be a text field, because I need to concatenate it with two other fields and retain the leading zeroes. Therefore, my spreadsheet will have four columns:
Company Number = 0008, Employee Number = 000000001, Dist Number = 03, Concatenated field = 000800000000103. I can't figure out how to add the leading zeroes, but keep that field a text field, so it shows the zeroes in the concatenated field. Thanks in advance for any assistance!
Hello all,
I am hoping to find an easy formula to add leading zeroes to a text string of different lengths. I have a column of data that contains hexidecimal codes. The hexidecimal codes should be eight characters in length but the leading zeroes are not there.
For example, a code of 003fa700 becomes 3fa7000 (six characters). A code of 0b3fa700 becomes b3fa700 (seven characters). I am looking for a formula which would restore the leading zeroes such that the length of the text string is exactly eight characters. I am having difficulty since the codes are not formatted as numbers. Also the leading zeroes are removed before the data is imported into Excel therefore the leading zeroes are not to be found in the cell values.
Is there a way to add a certain number of zeroes to the front of a text string in order to make that string exactly eight characters? Thank you very much for your help.
Note: I've tried this:
=IF(LEN(D7)=7,"0" & D7,IF(LEN(D7)=6,"00" & D7,D7))
The code is in D7 and this adds the correct number of zeroes based on the string length, but the nested IF formula is awkward to account for all lengths. Please help me simplify this.
Hello all,
I'm having more trouble with this than I should. I need to create a large number of consecutive item numbers. Format will be 2 letters and 6 numbers. ie. SH000001
I created a column with the letters and a second column with the numbers (leading zeroes included). But when I use Concatenate to combine the columns, all the leading zeroes disappear and I get SH1.
Ideas?
Forever grateful,
Jane
Hello,
I am querying some data using MS Query and I want to convert one Column before the results are displayed in my Worksheet.
I have a Column that is displaying product identifiers with leading zeroes. I don't want the leading zeroes but I do want the resulting "numbers" to still be treated as text values.
If I was to do this via formula, I would use:
=TEXT(A1,0)
But how can I do this in MS Query to achieve a similar result?
Thanks in advance.
Matty
I have a spreadsheet with custom formatting that allows leading zeroes to be visible. I need to load the data into Access (or Excel) as a text field. No matter how I try to format the cell or paste the data (paste special, cell * 1, etc) into another sheet, it treats it like a number and removes my leading zeroes.
We get a file in XML every month, which strips the leading zeroes from our 9digit ID number. So we have to reformat to add back in the 0s. We've tried all the regular tricks (using Custom Format, formatting to text, etc.) but XML removes all the formatting from your file. Is there a trick to getting the XML file to keep the leading zeroes?
We get a file in XML every month, which strips the leading zeroes from our 9digit ID number. So we have to reformat to add back in the 0s. We've tried all the regular tricks (using Custom Format, formatting to text, etc.) but XML removes all the formatting from your file. Is there a trick to getting the XML file to keep the leading zeroes?
I have a bunch of serial numbers, some alphanumeric, some just numeric, stored in a column. Excel identifies the numeric ones as numbers stored as text. The problem is that these numbers have leading zeroes which are important. The worksheet is totally reformatted by my existing code, so if there's a way for me to use VBA to direct Excel to ignore errors in these cells (and get rid of those annoying green flags), I have the opportunity to use it. An alternate solution is to somehow force Excel to keep the leading zeroes when it converts the numeric S/Ns back to numbers instead of text. I have no idea how to do either of these  or even if they're possible. I tried clicking on the flag and selecting "ignore error" while I recorded a macro, but Excel got hinky and didn't provide any useable code. Does anyone have any ideas?
Okay here is the problem. I am working with UPC codes, many of which have a leading zero. Excel is formatting them as numbers, and while I can play with the formatting to make the zero appear, in the formula bar the value is still shown without the zero. Since this is a UPC code it shouldn't even be treated as a number, but as text; however changing the format to text immediately removes the leading zero. The same thing happens if I use "Text to Columns"  it removes the leading zero because the actual value has removed it, just it's being formatted to display one. I need the value to have the leading zero, not the cell.
There are some 25,000 items here. Is there *any* way to change the *value* to text AND keep the leading zero? This data is to be imported into SQL Server so it needs to be text, but just changing the format still removes the leading zero, which will make the data invalid.
Good afternoon. I have a field in my spreadsheet that is sixdigit client numbers that may have leading zeroes. For that reason, I have to format it as text. The data is coming from different sources, so some of the cell entries are listed as numbers, some as text. I am using this information in a pivot table, but because of the difference in format, all the cells are not showing up. I am not sure at this point if it is the text that is not showing up or the numbers. I have tried format as text (but I understand that will not work with cells that were not formatted that way when you first entered the data), then Text to Column (as it shows in a Mr. Excel video); I also tried to concatenate an apostrophe with the number, but it did not get rid of the formula (it returned "=concatenate(A2,B2)" instead of a value)  I am at a loss! We have to keep the leading zeroes, but unfortunately, I am in a time crunch and need a solution  thank you for your help!
Example:
000111
000112
264325
264326
I'm trying to format a social with leading zeroes. I want the field (when exported) to continue to be a number, so that it can be reimported, so witching it to a format function won't work. I tried putting "@@@@@@@@@" in the format property, which adds the dashes, but does not keeping the leading zero.
Any help appreciated!!
Hi
I have a text field where the format is 0000000.
The last digit, 2 digits or 3 digits may contain a number other than 0.
What I want to do is trim the leading zeroes from the text string without changing the format to a number.
Is there a way to do this? I can't just use RIGHT as there may be differing numbers of zeroes to trim off
Hi,
I am trying to match against a table and have a few different variations that are causing it not to work.
I solved the green one and added to the formula to solve the orange one. Can anyone please provide a solution that also works for the red one (trailing zeroes)? I thought to use substitute() and take out all the zeroes  but I don't want to take a chance on getting false matches by removing interior zeroes.
Also, what I already have isn't very fast  I'd love to see something that would address that also.
Sheet1
A
B
C
D
E
1
Policy Num
My Lookup Attempt
Issue
Lookup Table
2
2297740000
5
leading zeroes in lookup table
0002296970000
3
228916
#N/A
trailing zeroes in lookup table
0002289420000
4
POS2298360000
7
leading text in policy num
0002292370000
5
POS2292370000
3
leading text in policy num
0002290790000
6
INE2290790000
4
leading text in policy num
0002297740000
7
0002289160000
8
0002298360000
9
0002298740000
Excel 2003
Array Formulas
Cell
Formula
B2
=MATCH( A2,$E$2:$E$9+0,0 )
B4
=MATCH( MID( A4,MIN( FIND( {1,2,3,4,5,6,7,8,9,0},A4&"1234567890" ) ),255 )+0,$E$2:$E$9+0,0 )
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
Thanks,
Tai
Fellow Forum Members,
I'm using Excel 2007. On Sheet_1 there are 300 numbers formated using a Special format. The numbers have leading zeros and look like this:
0007
0120
0450
0002
0040 ... etc
When I paste them into Sheet_2 Excel drops all of the leading zeros. I need to hold on to the leading zeros, and I them formatted as TEXT or real NUMBERS if possible. How can I use the TEXT function so that Excel keeps all leading zeros? I want to setup a link between Sheet1 and Sheet2 and in Sheet2 I want to use a function similar to: " =TEXT(Sheet1'A1, Sheet2'A1) and have the leading zeros stay intact. In short, I'm looking for a function that converts leading zeroes automatically generated using special formatting makes them behave as real numbers. Any help greatly appreciated. Thanks.
Hi all, I have a spreadsheet with many leading zeroesI used custom format to do this. Total digits is always 11 but i need to have only first 6 from left. The formula =LEFT(A2,6)is not working because of the leading zeroes...see attached file... Any help will be much appreciated.. Thanks in advance.
I have two cells that I need to concatenate with the result needing to match a specific format. The first cell is text, between 1 and 4 letters. The second cell is numbers, six digits with leading zeroes.
Example:
Cell A2TSUP
Cell B2000018
When concatenated, the result needs to say TSUP000018. I know how to concatenate the cells but the result I am getting is TSUP18; the leading zeroes are missing.
How can I make the leading zeroes show in the concatenated cell?
Thanks in advance.
I import some data from a mainframe that gives me check numbers, dates, amount of check, etc and is of fixed length. This creates leading zeroes and looks something like this:
00180271 0000018079 20021203 where 180.79 is the amount of check.
00180272 0000995903 20021203 where 9959.03 is the amount of check.
I need to remove the leading zeroes, but there are varying amounts of them depending on the amount of the check. Also I need to insert a decimal so 995903 becomes 9959.03 and 18079 becomes 180.79.
Thanks very much,
Match
I have a large fixed width txt file that I had to import into Excel in order to manipulate it. Now I need to save the file back in its original format (data has to be in specific spaces) but no matter what I try I lose leading zeroes. I've tried exporting to Access and then converting from there but all I still lose the leading zeroes. Does anyone have a solution? Thanks.
Again, I have a large spreadsheet.
I have a column that has a number from 17 digits long in it. I need it to keep leading zeroes and always have 7 digits in it.
Is this possible?
I am trying to import an aspx page that uses a gridview control to display data. User needs it in Excel. I do a web query and it works out, but Excel cuts off the leading zeroes in the phone field, which is a text field in the underlying table. I played with all the options, preserve formatting (or not), no formatting, etc. Excel keeps changes to general and therefore deletes the leading zeroes.
Help.
Here's my problem: Several months ago I had edited an excel price list file for importing into a inventory management program. This price list has a lot of numbers that begin with a 0 (zero), and Excel gives the error "Number stored as text". Well, I didn't know at the time, and I'm very inexperienced with Excel, but I must have converted those numbers stored as text to numbers, and it eliminated all the leading zeros. Now, it came time to update the price list, and I have all these incomplete item numbers, so I can't update the program properly. I'm hoping to find a way to add zeros in front of these incomplete numbers, in bulk (there's several thousand rows of them). But I don't know how. The problem is that the incomplete numbers are mixed right in with the correct numbers. I know how to add something to all the numbers, but I can't figure out how to isolate the bad numbers. There'd have to be a way, because they are unique. The item numbers are supposed to be 6 digits (there are a few 8 digit numbers also). The numbers that had their zeros cut off are 5 digits or less (Just a handful had more than one leading zero). So what I'm wondering is if there's any way to isolate all the five digit numbers. Then I could easily do what I want with them. I've tried sorting the item number column, but it doesn't group the five digit numbers. Here's an example of the numbers:
Correct item number (btw, all the numbers have the same two character letter prefix; the zeroes were deleted before I added the prefix):
AB123456
Incorrect:
AB12345 (should have been AB012345)
This is putting me in a real pinch and I'm hoping there's a way to do it other than scrolling through the whole thing and manually adding zeros before the item numbers that only have 5 digit numbers
I have a CSV file containing five columns.
column1 is row counter, numeric data.
column2 is an employee number, alphanumeric data.
column3 is the employee first name, alpha data.
column4 is the employee last name, alpha data.
column5 is the department number, numeric data.
While the employee number is alphanumeric, some employee numbers do not contains alpha data (they look like numbers).
The CSV file is generated by PL/SQL Developer. I have examined the file with Notepad and everything looks correct. All data is enclosed in doulbe quotes and delimited with comas.
The problem I am having is that when I open the CSV file in Excel (2000 version) the employee numbers that do not include alpha characters are interpreted as numeric data and formatted accordingly. This is a problem because some of these employee numbers have leading zeroes which are significant.
As I mentioned already, the data in CSV file is correct  the employee numbers have leading zeroes.
The funny thing is that when one of my coworkers opens the same file, they see the leading zeroes. This leads me to believe that the problem is caused by the way I have Excel configured but I can't find the correct setting to change.
Can anybody help? The optimal solution would not include reformatting the worksheet after opeing the CSV file.
Thanks in advance for your help.
Hi; I have a large spreadsheet with ID#s that have preleading zeroes. Example; 0003579. Obviously, the zeroes drop when you type it into Excel, and a quick and dirty solution is to throw in a formula in the cell next to it, like this; =TEXT(A1,"0000000") .
However, I want to create a macro to populate the preleading zero each time, and having the macro insert that formula can be problematic, since their is always a random number of IDs on the spreadsheet, and blank cells in Column A would return values of all zeroes, plus the fact that I dont want the formula in the cell.
Would anyone know a macro to insert those preleading zeroes that doesnt end up having to use another column?
Example below; I only want cells A1A6 to have info in them, with the IDs having the preleading zeroes in front of them.
Any direction would be a great help!!
A
B
1
6695
2
1258
3
3334
4
4587
5
9823
6
9723
7
8
I am working with two data sets that have two different formats
for an Assessor's Parcel Number (APN)
One is a simple eightdigit number: 00102001
The other is hyphenated, and may or may not have
an additional two digits: 0010200100, or 00102001
I need to remove the hyphens, and truncate the left most digits in
those that contain 10 digits, to convert the hyphenated APN's to
the simple eightdigit format.
When I use the replace command to replace the hyphens with nothing,
leading zeroes are deleted. e.g.: 0000000000 becomes simply 0
and 00104014 becomes 104014.
This is happening even when the numbers have been formatted as text.
How can I prevent the zeroes from being deleted?
Also, how can I truncate the numbers to the rightmost eight digits?

