
Adding Text Before A Number


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hello,
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 110, I want three zeros before the number, so:
ML0001
ML0002
....and so on.
Numbers 1099, I want two zeros, before the number, so:
ML0050
ML0051
...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?
thanks
Similar Excel Video Tutorials
SUMPRODUCT function
 Topics for this video: 1)Learn about the basics of SUMPRODUCT 2)The SUMPRODUCT function multiplies arrays of the same size and then adds the ...
Similar Topics
Hello,
Ive done a cross post he
http://www.excelforum.com/excelgene...anumber.html 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 110, I want three zeros before the number, so:
ML0001
ML0002
....and so on.
Numbers 1099, I want two zeros, before the number, so:
ML0050
ML0051
...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?
thanks
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
or
784512  0000784512
oe
45  0000000045
I know I can find the number of zeros by doing a 10Len(A1) formula. But how do I go about adding that many number of zeros in front.
Thanks
Best regards
Santa
Sedols are a security identifier. They have length 7 and many start with zeros, for example:
0756059
0321528
B16WQD5
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.
Thanks,
Martin
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
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
di
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.
Cheers
Pip
I have a column with hundreds of 7digit numbers. I want to convert all of the numbers to 10digit numbers with zeros inserted into the number, filling the first, fifth, and eigth digit. For instance, I have a 7digit 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 10digit 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 have a collum with numbers with 6 digits and all of the numbers begins with zero, in some cases they have two zeros before the number. The cell format is "general" and I woul appreciate very much if you could teach me how to remove those zeros or how can a formula recognize them.
Please find attached a specimen of one of the numbers.
Thank you.
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.
TIA
Heather
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
Hi, I have a special need to let Excel generate random numbers in a string of text like this.
="PCTECHTV"&(INT(RAND()*100000))&"INT3"
The result PCTECHTV34729INT3 with the random being 34729 or what ever it generates.
Now this if working for me except I need 5 numbers always and it is some time generating 4. I guess this is because some times INT(RAND()*100000) comes up with number s with zeros with it and that is fine but I want the zeros to show. I don't want to us INT(RAND()*100000) in a separate cell and format that to display 5 numbers and then have to add it to the =. Does any one know of a way to do this in the formula? Or?
Thanks
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.
Gents
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)
Hello,
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.
Thanks
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,
Cazz
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.
Thanks.
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.
Asclepius
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 110. I am trying to import these into Access and sort them based on the first number if there is an array such as the 110. I know how to get the leading zeros on the single numbers such as 4 but how would I do that on the 110? I would like for my current 110 to show up at 001010 while at the same time 1115 would show up as 011015. Or I could settle with only adding the leading zeros on the first part so that it ends up as 00110 and 01115.
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:
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, 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.
Hello:
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 3digit 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 6digit numbers with no leadign zeros. All of the nonzero 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
Thanks,
K
Hi!
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.
THE PROBLEM!
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!
Thanks,
Cj
I have a list of numbers, each with a bunch of zeros listed before the number
ex. 000000005608
00000000000000067890
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.
Thanks.

