Custom Cell Format (Should be an easy one!)

[markf5998]

I have a =concatenate() formula that pulls together 3 different cell values seperated by a hyphen. The result is always numeric, in the format 00000-000-00000.

My problem is that if the number in the center has leading zeroes, they are automatically removed. I tried applying a custom format to the cell (00000-000-00000), but it isn't working.

=CONCATENATE(VLOOKUP($C$47,'Buy Summary'!$A$6:$P$55,8,FALSE),"-",VLOOKUP($C$47,'Buy Summary'!$A$6:$P$55,16,FALSE),"-",B13)

Any help would be greatly appreciated!

 

[Mike Blackman]

Hi Mark,

Something like this will hold the formatting, just change my cell references with your references or formula.

=TEXT(A1,"00000")&"-"&TEXT(B1,"000")&"-"&TEXT(C1,"00000")

 

[DauntingGecko]

Clue.

=TEXT(1,"00000")

 

[markf5998]

Perfect, thank you both!

 

[Gerald Higgins]

Perhaps replace

VLOOKUP($C$47,'Buy Summary'!$A$6:$P$55,16,FALSE)

with something like this (not tested, you may need to play around with it)

TEXT(VLOOKUP($C$47,'Buy Summary'!$A$6:$P$55,16,FALSE),"000")

Edit to add - sorry, FAR too slow

 

[jim may]

Here's a quick data Table (on Sheet1):

Excel Workbook
	A	B	C	D	E	F
1	Company 1	12345	Company 1	45	Company 1	6789
2	Company 2	3	Company 2	321	Company 2	6
3	Company 3	9	Company 3	132	Company 3	7
4	Company 4	6	Company 4		Company 4	42
5	Company 5	2	Company 5	23	Company 5	312
6	Company 6	4	Company 6		Company 6	456
7	Company 7	8	Company 7	534	Company 7	76
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D4	=IF(ISNA(VLOOKUP(A4,$E$1:$F$3,2,0)),"",VLOOKUP(A4,$E$1:$F$3,2,0))
D6	=IF(ISNA(VLOOKUP(A6,$E$1:$F$3,2,0)),"",VLOOKUP(A6,$E$1:$F$3,2,0))
C1	=IF(ISNA(VLOOKUP(A1,$E$1:$F$3,1,0)),"",VLOOKUP(A1,$E$1:$F$3,1,0))
C2	=A2
C3	=A3
C4	=A4
C5	=A5
C6	=A6
C7	=A7
E2	=C2
E3	=C3
E4	=C4
E5	=C5
E6	=C6
E7	=C7

Then here on Sheet2 - I want to Concatenate 3 fields, based on my Lookup Value
Company 1 ( In cell B1) - Note Formula in Cell B3

Hope that Helps...

Excel Workbook
	A	B
1	Look up Value -->>	Company 1
2
3		12345-045-06789
Sheet2

Excel 2007

Cell Formulas
Range	Formula
B3	=CONCATENATE(TEXT(VLOOKUP($B$1,Sheet1!$A$1:$F$7,2,FALSE),"00000-"),TEXT(VLOOKUP(Sheet2!$B$1,Sheet1!$A$1:$F$7,4,FALSE),"000-"),TEXT(VLOOKUP($B$1,Sheet1!$A$1:$F$7,6,FALSE),"00000"))

 

[jim may]

PS-- The formulas shown for Sheet1 are insignificant (they are carry overs from a file I used to build this sample)

Jim