Leading Zeros To Complete 3 Digit Number

[jgthtrotih]

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
	E	F
28	RANDBETWEEN("000",999)
29
30
31	356	356
32	454	454
33	48	048
34	367	367
35	785	785
36	46	046
37	856	856
38	986	986
39	46	046
40	85	085
41	8	008
Sheet

Excel Workbook
	E	F	G	H	I	J	K	L	M
7	8		401		934		8-401-934
8
9	450		587		16		450-587-16
Sheet14

Have Any Questions Let Me Know!

Using Excel 2003!

Thanks!

 

[Jonmo1]

OK, if this is your formula in K7
=IF(E7="","000-000-000",E7&"-"&G7&"-"&I7)

But you want the 3 numbers to be in 3 digit format right?
You have to apply the Text function to each part of the number (E7 G7 and I7)

Try
=TEXT(E7,"000-")&TEXT(G7,"000-")&TEXT(I7,"000")

 

[jgthtrotih]

Jonmo1,

*Keep Original Cell References These Are Example Cell References*

That is close to what I'm after..leme try it this way...

If 436 is Entered in Cell E31
If 005 is Entered in Cell G31
If 055 is Entered in Cell I31

Than it would come together in K31

if the next set of numbers are the same..nothing happens...i dont want duplicate sets of numbers.

If the next set of number are entered as followed than it would come together in cell K32

If 234 is Entered in Cell E31
If 256 is Entered in Cell G31
If 672 is Entered in Cell I31
if the next set of number are entered as followed than it would come together in cell K32..and so on as "Exampled" below

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M	N	O
30
31			436		005		055		436-005-055
32									234-256-672
33									264-986-234
34									975-468-246
35									906-869-969
36									486-960-894
37									057-996-473
38									696-896-689
39									696-590-378
40
Sheet14

Please ask any further questions

Formula are preferred.

Thanks!

 

[jgthtrotih]

Bump!

Anyone?

Thanks!

 

[jgthtrotih]

Bump!

Anyone?

Thanks!

 

[jgthtrotih]

Bump!


Thanks!

 

[jgthtrotih]

Please help read post 12.

Thanks!

 

[mrnacar]

Please help read post 12.

Thanks!

It looks like jonmo1 got what you need except you don't what any duplicates.

Why not try a macro approach. Now I'm no expert so I just recorded the following code to remove the formula in K31:K40 so advanced filter could remove the duplicates and place it in N31:N40. Feel free to change the range as you need:

Sub removeduplicates()
    'removes formulas which basically does a paste special values only'
    Range("K31:K40").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'removes duplicates and places it in column N'
    Range("K31:K40").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "N31:N40"), Unique:=True
End Sub

 

[Sandeep Warrier]

Hi

If I understand correctly, you will be entering numbers in only 3 cells (E31, G31 & I31 via formula).

Every time the formula is re-calculated you want to populate Column K starting from K31. You don't want to populate Column K if the 3 numbers have already occurred. Am I right?

If yes then you will need to use a Worksheet Calculate event to do this.

 

[jgthtrotih]

Sandeep,

Yes..exactly what i was looking for...how would i go about setting the code up?..as I'm not good with the "Code Road"...i like "formula Street" better less bumpy.

mrnacar,

Couldn't get it to work.

Thanks!

 

[Sandeep Warrier]

Does number order matter while checking for duplication?

For example: if 431, 005, 010 have already occurred, you will get 431-005-010.... now if a new lot of numbers say 010, 431, 005... is this counted as a duplicate (since all 3 numbers have already occurred) or new (since order of occurrence is different)?