Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Adding Text Before A Number

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


I have what might be a simple question for all you smart Excel users out there.
I have a list of numbers, that goes from 1 - 3065. My issue is, I want to add the text ML before each number.

So from numbers 1-10, I want three zeros before the number, so:
....and so on.

Numbers 10-99, I want two zeros, before the number, so:
...and so on...

Then numbers, in the hundreds, I only want 1 zero, then numbers in the thousands, no zeros need to be inserted, just the ML text. Does anyone know of a formula or VBA code that might work?


View Answers     

Similar Excel Tutorials

How to Add Boxes, Buttons, Arrows, Speech-Bubbles, Hearts, and More to a Spreadsheet in Excel
In this tutorial I am going to cover inserting and editing Shapes in an Excel workbook, as well as adding text to t ...
How to Add a New Line to a Message Box (MsgBox) in Excel VBA Macros
I'll show you how to create a message box popup window in Excel that contains text on multiple lines.  This allows ...
Store Large Numbers in Excel
I will show you how to display large, even huge, numbers in Excel.  In Excel, you can't show numbers that are too ...
Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...

Helpful Excel Macros

Format Cells as a Scientific Number in Excel Number Formatting
- This free Excel macro formats selected cells in the Scientific number format in Excel. This means the cell will be put
Get the Number(s) Out of a Cell that Contains Both Text and Numbers in Excel - UDF
- This free Excel UDF allows you to get the numbers out of a cell that contain both text and numbers. This is a great fun
Generate a Non-Repeating List of Random Numbers in Excel - UDF
- Generate a series of non-repeating random numbers in Excel with this UDF (user defined function). This is a great funct
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Delete Only the Text from Cells
- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell

Similar Topics


Ive done a cross post he For the extra help.

I have what might be a simple question for all you smart Excel users out there.
I have a list of numbers, that goes from 1 - 3065. My issue is, I want to add the text ML before each number.

So from numbers 1-10, I want three zeros before the number, so:
....and so on.

Numbers 10-99, I want two zeros, before the number, so:
...and so on...

Then numbers, in the hundreds, I only want 1 zero, then numbers in the thousands, no zeros need to be inserted, just the ML text. Does anyone know of a formula or VBA code that might work?


Hi there

I've got a list of numbers and I have to make them ten digit numbers by adding leading zeros

For instance

12345678 --- 00123456789


784512 --- 0000784512


45 ---- 0000000045

I know I can find the number of zeros by doing a 10-Len(A1) formula. But how do I go about adding that many number of zeros in front.

Best regards

Sedols are a security identifier. They have length 7 and many start with zeros, for example:


When you paste these numbers into excel they are displayed as above. BUT if you click into the formula bar the zeros are lost. Excel recognises these identifiers as numbers (even when the cell is formatted as text and the data is pasted as text). For example if cell A1 = 0756059, len(A1)=6 not 7.

Anyone know how to solve this. I'm sure there is some option somewhere to get excel to recognise numbers as text.


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



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

Hi there, I have a string of numbers and I would like to add zeros to the end of it.

For example my numbers are formatted like this: 1234 and I would like to add zeros to the end so it looks like this: 123400 (no decimal).

I would do it manually but I have a column of over 2000 different numbers.

Thanks for your help, you guys are great!

I would like to add 2 zeros to the end of the number. I have a column of 4 digit numbers and I want the to have 2 zeros at the end. Can someone please help me with this simple problem. I'm sure there is an ease solution. I just can not get it to work.

Thank you

Hi Guys,

I regularly get a spread sheet with various payroll numbers from different clients. This workbook has to be formatted as text and saved as a .csv file to upload onto our internal system. The problem is that when the file is formatted as text, many of the payroll numbers lose their leading zeros which I want to keep. To further compound the issue, some zeros remain whilst others disappear, some have 3 leading zeros some have 1 and some dont have any leading zeros at all (they are meant to be like that) I have tried using the text function etc to no avail.

Any help is most welcome and please let me know if i can expand further.



I have 2 columns of information, one is text and the other is numbers. The numbers are 4-digits, several with zeros at the beginning. When I simply use =A1 & B1, which is what I would normally do (column A is text, all the same, and column B is numbers), the leading zeros are being stripped away. I have not been able to use any method that I know to make this NOT happen. I have to have the zeros in there. For what I'm using this for, they all have to have the same number of digits.


I've attached the spreadsheet. I have played around with a few things, and I haven't removed a couple columns.

Thank You!

I have a column with hundreds of 7-digit numbers. I want to convert all of the numbers to 10-digit numbers with zeros inserted into the number, filling the first, fifth, and eigth digit. For instance, I have a 7-digit number xxxxxxx and I want to change it to yxxxyxxyxx where the y's are zeros. The solution I'm looking for will convert the entire column into 10-digit numbers in this manner.

I hope whoever reads this can understand it, as it is somewhat difficult to explain. I'm not sure if Excel is capable of inserting digits into numbers like this, but I sure hope so, as changing them all individually would be extremely tedious.

I have a list of soc sec numbers that were brought in as numbers so it deleted the first one or two zeros. How can I easily add the one or two zeros back to the beginning of the number?

I am trying to do a vlookup between 2 spreadsheets and in the one the data is
stored as text and the other as a number with leading zeros (ie. 001) when I
change the format to text it keeps the 001 so I was just wondering if there
is some code to strip the leading zeros out before I reformat the numbers to
text. Your help is appreciated.

I have a list of numbers, 2 digit to 7 digits. I need a macro that will run in the colmn selected and change all numbers to 9 digit numbers,adding #5 and rest zeros to start of number, ..VALUE(5000&A1) will be too tedious.I have 3 digit to 7 digit numbers. like 500000123, 500001234

I receive a report that has personnel numbers starting with zeros. They show in Excel with an apostrophe at the beginning to tell Excel to consider the number as text. I need the personnel number to show as a text value with the leading zeros (to copy and paste to another program). In the worksheet, I can use
=Text (reference cell,"00000000")
to convert the number to text with the leading zeros and without the apostrophe. I want to select a range and use VBA to automate the process but I can't find the correct macro function anywhere.

Thank you.


I've run into an issue with the autofilter function in my workbook. I have a column containing part numbers which can be a variation of numbers, text and a pick and mix of both. The row is formatted as text to allow leading zeros on the part numbers. The excel error checking highlights with a comment those records which it believes are numbers stored as text. These are the only cells I can get to display after using an autofilter. Any other numbers are not displayed on application of the autofilter.

Any ideas as to how I cure this problem other than retyping in each part number (which does work but would be time consuming)


I am trying to use sumif on columns of numbers as shown in the following example:

Acct # Amount
000123456 100.00
000123456 199.00
00123456 500.00
00123456 500.00

To get the total for each account number, I use sumif:

=sumif(a:a,a1,b:b) and paste the formula the rest of the way down in a new column to the right.

My problem is that sumif ignores the leading zeros and treats all of the account numbers shown in the example as the same account number.

In the system I am working in, they are distinct accounts, so I cannot remove or pad the number of zeros.

Any ideas on how to get Excel to recognize them as distinct numbers? I have tried converting them to text as well, didn't work.


Dear colleagues, I wonder if anyone can help me,

I have received a spreadsheet with a list of names and phone numbers, but as it has clearly been saved before with this as number data, all the leading zeros are chopped off! I need to use this data in text broadcast software that can't cope with this incompletion.

Is there any simple way to insert either a 0 or a +44 in front of all the numbers in this column? Or am I doomed to RSI and a headache doing it manually?!

Many thanks, in anticipation,


I have a column of numbers that contain numbers and words. I want to seperate the numbers from the words, which I know how to do by using the Data/text to columns. My problem is that the leading zeros are dropping out. I've used the formula before to add them back in, (=right(cellref+100000,5), but in this case it won't work because my numbers are all different lengths.

Search and replace drops the leading zeros also, which was something else I tried.

I know it is something with my data source, but this is all that I have to work with.

Any help is appreciated.


Please help,

I receive excel reports that have an account and branch numbner of customers all in one cell. The two numbers are separated by a small square symbol. I need to separate the two numbers. This can be easily done by the 'Text to Columns' facility, with wich I can shift the latter 4 digits to another column. These numbers usually start with zeros, i.e. 0001, 0014 e.t.c.

I'm not convinced excel is reading these branch numbers as simple numeric values due to the presence of this small square. When I click on the cell in question the value in question is displyed in the formula bar, however the formula bar itself goes twice as thick, inexpicably, to accomodate two lines of text which isn't necessary. This makes me think excel doesn't value it as a number, and i think the fact that the value could potentialy comprise all zeros has something to do with it.

What does the small square separator mean?

I'd like the branch number in a format that is compatible with using VLOOKUP functions, and I don't think it is in its current format.

Please help urgenty!

Much appreciated.


I have several rather lengthy spreadsheets that have a column of text data. Some cells might have just one number in it such as 4. Then some cells might have something like 1-10. I am trying to import these into Access and sort them based on the first number if there is an array such as the 1-10. I know how to get the leading zeros on the single numbers such as 4 but how would I do that on the 1-10? I would like for my current 1-10 to show up at 001-010 while at the same time 11-15 would show up as 011-015. Or I could settle with only adding the leading zeros on the first part so that it ends up as 001-10 and 011-15.
If possible I would like to be able to (using the same formula) also change a single digit number such as 4 to 004.
All of these cells are formatted as text because a number format will not accept the dash.
Does anyone have any suggestions on how this could be accomplished? Thanks.

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:

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, I am trying to create a macro which will take a column of number (in this case, loan numbers) which are stored as long integer and will format the numbers (10 digits) as text with leading zeros.

The formula =TEXT( cell reference, "0000000000") will do the correct conversion and I can manually convert the numbers by inserting a column, entering the formula for the first number, filling the formula down, copying and pasting (values) and deleting the original column. However I would like to automate this process. I am having difficulty applying the formula to each loan number. Should I use a loop to increment rows?
Additionally, I would like to make the macro generic, meaning it not be dependent on the loan numbers always being in the same column.

I have been exploring various methods for cell referencing but have been foiled with each attempt.


I've read all the postings in the message board regarding how to get around the "leading zeros" problem and none of the messages help solve my problem.

I've got some data which I can not modify in its initial software application. When I exported it into excel, all of the leading zeros in one of my columns disappeared.

Some of the cells in this particular column contain letters only, some should contain 3-digit numbers with 1 leading zero, some should contain 4 digit numbers with 2 leading zeros, some should contain 5 digit numbers with 1 or 2 leading zeros and some should contain 6-digit numbers with no leadign zeros. All of the non-zero numbers in those cells properly displayed, but all of the leading zeros disappeared. There is a tremendous amount of data in this column and there is no way for me to know, just by looking at my data, which of the cells are missing 1 leading zero and which of the cells are missing 2 or 3 leading zeros because the data in that column does follow one specific format or one specific set of criteria.

Converting the cells to text didn't solve the problem. Formatting the cells using "custom" doesn't work either because "custom" only allows me to format text when all of the cells meet one specific criteria. For example, if I want all cells to contain six digits, using "custom" will correctly force excel to fill in three leadings zeros in all cells that contain only three numbers; however, my situation isn't that simple.

SOME of my cells require 2 leadings zeros (with 5 numbers in total in that cell); SOME require 3 leadings zeros (with six numbers in total in that cell) SOME require NO leadings zeros (with 4 numbers in total in that cell). Some of my cells are letters only. I need a formula or a macro or something that will allow Excel to read all of my cells in this particular column exactly as they were intended to be read. Converting the cells to text doesn't work and using "custom" doesn't work because custom only works when all of the cells meet the same criteria (i.e, all cells must contain XX number chateracters).

It's almost as if I need to tell excel to do the following: Insert 1 leading zero in all cases where the original data contained 1 leading zero, insert 2 leading zeros in all cases where the original data contained 2 leading zeros, etc., etc.

Does anyone know of a formula or way to include 1, 2 or 3 leading zeros ONLY in those cells in which the original data contained 1, 2 or 3 leading zeros?

I'm not very advanced with this stuff so if anyone has any solutions, please write back to me with instructions that are written as if you're talking to a complete idiot


I have been struggling with this for a few hours and have exhausted every avenue i can think of.
I have a text file with several hundred strings of text. Each string is of varying length. I wish to extract the last four numbers from each string. The numbers are separated by spaces and vary between 0 and 9999, so i cannot do a simple RIGHT function.
What i have tried is the following;
I found quite a nice macro that reverses the text in a string. I use this on each line, thereby putting the numbers i want at the start of the string, but backwards. I then use text to columns, using space as the delimiter. I then use the same reverse function as above on each of the fields i require.
If any of the numbers end in 0, after i reverse the string and then delimit it the zero is lost because it is treated as a leading zero. I have tried formatting the fields both before and after the delimitation, either as text fields or by adding in zeros with a custom format and nothing works.
Does anyone know of a way to keep leading zeros after a string is delimited? Any other suggestions as to how to solve my problem are more than welcome too!

I have a list of numbers, each with a bunch of zeros listed before the number
ex. 000000005608
I dont know why is showing like this but is there a way of removing the zeros (before number)
reslut: 5608 and 67890

Hope you can help.

I know it sounds complicated. But what I have is a spread sheet of over 3000 numbers.

Now this is what the number looks like:


what I need is basically a formula or something to take out the zeros (They are not really zeros on the spreadsheet, its just an example to make it easier to display). And leave the six digits in the middle. Is this possible?

I would do it manually but 3000 numbers could take ages! lol