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

i have a field that has a number in it for example

100

115

116

12

i want these to always be 4 digits and put zeros in front of the number

how can i do this

## Similar Excel Video Tutorials

## Similar Topics

Hello,

Can someone please help with adding zeros to the front of a number? Each cell needs to be 12 digits. Any cell with less than 12 digits need zeros in front. Examples below:

CURRENTLY: 4122235443 (10-digits)

NEEDS TO BE: 004122235443 (12-digits)

CURRENTLY: 57655599900 (11-digits)

NEEDS TO BE: 057677799900 (12-digits)

CURRENTLY: 108877443322 (12-digits)

NEEDS TO BE: no changes needed

Thanks in advance!

I have a field in my Access query that is called RequisitionNbr. The numeric data in this field is not consistent as far as the digits. There are some cells with 1 digit number, 2 digit numbers 3 digit numbers, 4 digit numbers etc. I want to add zeros in front of all cells in that field with < 4 digits. Example if this field has 1 digit number like 3, I want the result to be 0003. If it has 2 digits like 24, I want the result to be 0024. If it has a 3 digit number like 424, I want the result to be 0424. Anything 4 digit and above should just be left alone. How can I put this in a query in access. Thanks for your help

Dear ALL,

I using JDE to export data to excel. While Excel, keep auto eliminate the zeros in front of my item number.

Eg:

00123 ->123

012->12

00001345-.1345

I try to use format cell to help me. However, my number as you can see no fix to certain digits, some might be 4 / 5 / 6 digits. Furthermore, sometimes the zero in front of the number is 1 or 2 or 3 zeros. Once export to excel, it auto eliminate all zero.

May I know is there any way to off this default function ??? Please advice

Hello,

I've got a spreadsheet which contains a field that is formatted to always have 8 digits. If only 5 are entered, then 3 zeros are added to the front of it (ie. 00012345). It is formatted as a NUMBER.

However, once this is merged into an external form in Word, the extra zeros are dropped. Is there a way to convert the field from number to text in the process of merging? Or another way to keep the zeros?

Thanks.

Hi,

I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.

Thanks in advance

Sam

When I do a v-lookup...the worksheet that is displaying the values from the table array is removing zeros from the front of the numbers. Can you tell me how to stop this from happening? The number I am populated is a foriegn key and it is critical the whole number displays all digits.

Help!!!!

Hello,

Take a look at the sample sheet. I get the information as an import that looks like the info in column A.

I want it to look like the data in Column B. If I try to format as a number it always turns the last number to a 0. I also dont want the 0's on front of the digits either.

I dont really care how its done. But I would like a routine to turn column A into and like the example in Column B. No zeros on the front and maintaining all numbers.

Thank you for suggestions.

Hello,

I'm trying to do this for ages.

Basically, in sheet1, column D, I have a list of names and numbers as follow:

bear - 035888877
MS - 02555887
Coal - 58887441
SE - 02587410

In Sheet2, column Q, I want to extract the number and keep the zeros in front (in case there is a zero). I've tried,

=REPLACE(LEFT(D1,LOOKUP(10,MID(A1,ROW(INDIRECT("1:30")),1)+0,ROW(INDIRECT("1:30")))),1,MIN(FIND(0,SUBSTITUTE(D1&0,{1,2,3,4,5,6,7,8,9},0)))-1,"")+0 (I found it online).

But I cannot keep the zeros in front! I tried changing cell format to text, didn't help either. I cannot do cutom-0000000 as the lenth of the number can change.

I'm trying to build a macro (recording) doing this but the pb is always the little 0. Please advise if you have an idea?

Many thanks.
Fak

Is there a way to keep zeros in front of a number that ALSO shows in the formula bar?

I have product numbers such as 0022 but when I use a vlookup, it doesn't work because it is looking for 22.

I need to keep the numbers in original format for it to work.

Hello,

An account number should consist of 7 digits. Sheet 1-When the data was downloaded from an application any account that contains a zero in front of the number then it would drop the zero and becomes an 6 digits account (note: not all accounts have a zero in front). How can I normalize this so that when I try to reference/lookup to the correct account number it would know to match.

TIA for your efforts.

Have a s*&%load of ABA numbers. If they are 8 digits they need a zero in front of it.

have issues using the LN formula. Would prefer some decent VBA

basically needs to go down the column and add a zero in front of the number if its 8 digits. Theres about 3600 rows.

EDIT: just a slight edit (s*&%) - We do have kids around - Moderator

Hi,

We have products that need to be entered into a system using 7 digits. However very few are 7 digits long. When they are not 7 digits '0's need to be put in front of them.

For example 1234 won't work, 0001234 will.

Is there anyway to force a cell to be 7 digits long and replace any missing digits with 0's at the front? To save us tons of time.

Thank you in advance,

JP

I have this script below.

It's on a userform with "td" being a textbox.

The textbox can only have 3 numeric digits in it.

Here's what I'm trying to do.

If the textbox only contains two digits, I want the script to pad it with a zero in front.

If the textbox contains one digit, I want the script to pad it with 2 zeros in front.

Doable?

Code:

Private Sub CommandButton1_Click()
Range("o28").Select
ActiveCell.FormulaR1C1 = td
End Sub

Hi there I have a list of hundreds of account numbers and to make things easier for the person who will upload them into the system they need to be 16 digits long. an account number can be 4-16 digits long for those that are less than 16 we need zeros in front. Is there a way we can all loan #'s in column A have all the digits needed in column b?

I have a lot of record that contain number like shown below:

0000082181

0000005465

0000028997

I want to remove all 0 in front of this number in excel. Can anyone here help me.. I use excel 2007..

I already try using formula

Code:

=IF( LEFT(A1) = "0" , RIGHT(A1, LEN(A1)-5), A1)

but it only remove 5 character in front what about the number that have 6 '0'......

Please help..

Thanks in advance.

I would like to display a number with leading zeros in front of it.

i am sure this is probably really easy......i have to copy and paste values from an sap program over to excel spreadsheets, and I usually do about 15 at a time that end up in a column: 15 different cells. The value I am copying are ID numbers that all begin with zero and excel automatically removes the zeros at the front of each number.

Is there a formula/process for preventing this.

Thanks guys!

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 10-Len(A1) formula. But how do I go about adding that many number of zeros in front.

Thanks

Best regards

Santa

I am making a small macro to convert materials outside of SAP into materials that can be recognized by SAP. Here is a sample material transition I am looking to do.

Convert material 1031560 to 000000000001031560

Obviously, outside of SAP those extra zeros have no value so I remove them. However, sometime I have to add them back in when I want to query specific materials in BW.

The code below is getting me very close to where I want to go. All I need to figure out is how to add in a specific number of zeros to the front of a material number.

In the example above, I would have to add 11 zeros and format the number as text in order to get a legit 18 digit material number (in SAP, all our materials are 18 digits long).

Sample material numbers are in column A and the number of characters in the current material are identified in column b. How can I add in the number of zeros needed as I describe above? Thanks for the help in advance.

Sub SAPMaterial()

Dim x As Integer, rng As Range, c As Range

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Set rng = Range("A1:A" & LastRow)

For Each c In rng.Cells

'Materials in SAP are 18 characters long, so 18-number of characters in current material number will identify how many zeros I have to add to get a legit material.

c.Offset(0, 1).Formula = "=len(" & c.Address & ")"

'Not sure what to do here to add in the zeros needed to get to a legit 18 digit material

Next c

End Sub

Ok. I am uploading to a database and a Grade field needs to have two chracters ( so leading zeros in front of single digits) My current spreadsheet haso nly 1 character. I thought I had it right but apparently the column was just formatted to display 07 but what is actually in the field is 7. How do I add elading zeros so they are actually data, not just formatting?

Thanks!

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 put together a Macro or figure out some simple way which will allow me to change digits out of a string of numbers.

I think creating a macro would be the best bet, but problem is I have no idea where to start. The only type of macro Ive ever used is your basic click and record type, dont know much programming.

say i have a string of numbers

65987546

I need to delete the first two numbers (65) add 4 NEW numbers to the front of the string (4420) and then add two zeros to the end of the number.

I want to go from 65987546 to 442098754600

any ideas? There is a list of about 100K line items, and the only way I know how to do it is by writing a formula to multiply and add accordingly which is fine, but Im sure there is a faster way. I also can use the concotante function to add zeros the end of the string, but I dont know what to do about getting rid of the digits at the begining of the string

any help?

The number below is an incident number

200900000036 and is located in column C

in this column I need to have Excel automatically take the digits "200" of the front of this number.

any ideas for me?

I've been struggling with this for a while and desperately need someone's help. I need a macro that will take a 13 digit number, add a zero at the front, change the place of the 9th and 10th digit and then add two more zeros at the end.

For instance:

Take 08347004003486 in Column A and change it to 00834700434008600 in Column B.

Steps:

Add a zero at the front: 008347004003486.

Move the 9th and 10th digits in Column A to the 12th and 13th spots in Column B: 08347004003486 becomes 008347004340086.

Add two zeros at the end: 00834700430048600.

Finally, move to the next row (A2) and repeat these steps - so on an so forth until the end of the numbers in Column A.

________________

If anyone can help me with this, it would be greatly appreciated!!!

I've been struggling with this for a while and desperately need someone's help. I need a macro that will take a 13 digit number, add a zero at the front, change the place of the 9th and 10th digit and then add two more zeros at the end.

For instance:

Take 08347004003486 in Column A and change it to 00834700434008600 in Column B.

Steps:

Add a zero at the front: 0 08347004003486.

Move the 9th and 10th digits in Column A to the 12th and 13th spots in Column B: 08347004 00 3486 becomes 00834700434 00 86.

Add two zeros at the end: 008347004300486 00 .

Finally, move to the next row (A2) and repeat these steps - so on an so forth until the end of the numbers in Column A.

________________

If anyone can help me with this, it would be greatly appreciated!!!