Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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 ...
How to Add, Remove, and Rearrange Columns and Rows in Excel
Adding, removing and rearranging columns is as easy as resizing. You just select your columns or rows, right click ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...

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 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.


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.


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 am having an issue with text strings that contain numbers. I can strip off the number from string but when I do excel loses the trailing zeros in the string. I need to know how many decimal places there are.

String example
1245.00 p 2 decimals
11.000 bnz 3 decimals
900.1 cv 1 decimals
89.59 cv 2decimals

The variables that do not end in zero are easy to determine. I need something that will work for both.

I was thinking some sort of pattern search. The numbers are always separated by spaces.


I have a sheet that gets loaded into SAP through the GLSU add-on and the numbers must be 2-decimal places and must be numbers but for example, Excel takes 168.90 and cuts off the zero to 168.9. Changing the number format just changes the display, not the actual number. I've searched for a solution and the common one is =TEXT(A2,"0.00") but this doesn't work as it needs to be loaded into SAP as a number not text (the GLSU add-on also does a calculation that makes sure the numbers balance to zero so the values need to be numbers and not text).

I have looked at pages and pages of possible solutions but no luck. Anyone out there have a solution?

Thank you!

I'm trying to do this at work, which is Excel 2003 in XP.

The cells consist of UPC numbers... they're formatted as text.
I need to remove the first character, which happens to be the number 0 in each one.
I don't want to remove all zeros because sometimes the series of numbers include zeros.


I have a file that contains a column of numbers formatted as text. Upto 5000 records. The length of these numbers varies from 4 digits to 22 digits. In some cases these numbers may have 1 to 4 preceding zeros. I would like to quickly remove all the preceding zeros. I need to keep the format as text as this file gets exported to another program that reads only text. Short of manually deleting them which is laborious, I have not been able to discern a way to accomplish this.

Any help would be greatly appreciated. TIA


I receive bulk orders via an Excel spreadsheet, containing many individual
orders with reference numbers.

The reference numbers may be 7-9 digits long, however the field needs to
have 10 digits. The format is set to customize: 0000000000. In this manner,
we always see the 10 digits regardless of the actual number.

The problem we have is that we bulk-load this spreadsheet through a specific
custome built bulk-loader. This reference number field must have 10 actual
digits, not format digits. Therefore a number such as 00865243 becomes
865243 and then will not run through the bulk loader.

A work around is to format the column as text and then insert the leading
zeros manually. This is ok, when the order is between 5 and 20, however we
sometimes get 1500-2000 orders at a time. Manually adding the zeros will not

I have tried converting into comma deliminated, and it still dropped the
leading zeros.

Any suggestions you have will be greatly appreciated.