Leading Zeros To Complete 3 Digit Number

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
365
Platform
Windows, Mobile
Hey All

i need a little help with leading zeros in excel i want a complete 3 digit number as shown blow (first chart) i used the " ' " mark to get the zeros in..i want the second chart to look the same with the zeros...

Chart 1: Examples and what I'm trying to get Achieve.
Chart 2: My actual data set.
Excel Workbook
EF
28RANDBETWEEN("000",999)
29
30
31356356
32454454
3348048
34367367
35785785
3646046
37856856
38986986
3946046
4085085
418008
Sheet


Excel Workbook
EFGHIJKLM
784019348-401-934
8
945058716450-587-16
Sheet14


Have Any Questions Let Me Know!

Using Excel 2003!

Thanks!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=TEXT(RANDBETWEEN(0,999),"000")


Just remember that these are no longer numbers, they are "Numbers Stored As Text"
 

Markis

New Member
Joined
Jul 29, 2010
Messages
7
Simply highlight all the cells you would like this to apply to. Right click and go to "Format Cells". It should open the Number tab, and then go down to custom. In the field for "Type:" simply place "000" without the brackets.
 
Last edited:

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
365
Platform
Windows, Mobile
Jonmo1,

Thanks! that worked perfectly..Nice Blue Suit When You Start Rocking That?

Markis,

Thanks! Another Great Solution.

Thanks!
 

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348

ADVERTISEMENT

This might also work -

=RIGHT(CONCATENATE("000",RANDBETWEEN(0,999)),3)
 

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
365
Platform
Windows, Mobile
prabby25101981,

Another Perfect Solution!

Thanks!
 

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
365
Platform
Windows, Mobile

ADVERTISEMENT

Continuation of Question..As Requested By Smitty:

I need help making a list.

Details:

When the Randbetween Function comes up with the 3 sets of numbers than i merge the sets together to get the "Number set" in cell K7

I want it to make a list of "Number Sets" created..if a duplicate "Number Set" is present i don't want it included in the list.

Please Ask Questions If Need Be!

Thanks!
 

jgthtrotih

Well-known Member
Joined
Aug 28, 2009
Messages
568
Office Version
365
Platform
Windows, Mobile
Bump!

Please Read Post #7!

Any Help Is Greatly Appreciated!

Thanks!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,672
Messages
5,512,798
Members
408,914
Latest member
ariannem

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top