Custom formatted cell with leading zeros not carrying over to CONCATENATE

[mjaeger1]

Hi All,

I have a quoting tool im working on and hitting a snag.

I was forced to use custom formatting on my 'Length' column so that I could use a =IF formula on the 'breakout' column to automatically populate that cell depending on the value entered in 'length'.

"length' must have leading zeros to make it a 2 digit number (in this case it is one leading zero in the first case (01, 05, 08, 10, 15), and four leading zeros in the second (0001, 0006, 0010, 0120)

The issue is that when i try to use a =concatenate function, it does not carry to leading zeros.

Is there any way to get around this without using a =text function, as there is not always a set number of leading zeros depending on the value?

NBC Fiber QuickQuote - Final - V 1.3 DL.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
29	Multimode (OM4)						Length in Meters (2 digit i.e. 02, 07, 10, 10.5)		Breakout
30	Side A		Side B		Base Part Number (xx-Length, yy-Breakout, p-Polarity)
31	MPO		LC/UPC 2-strand	SFH-FH002MR	SFH-FH002MRxxMyyK	M	01		32	#N/A	K	M	SFH-FH002MR01M32K
32	MPO		LC/UPC 4-strand	SFH-FH004MR	SFH-FH004MRxxMyyK	M	01		18	#N/A	K	M
33	MPO		LC/UPC 8-strand	SFH-FH008MR	SFH-FH008MRxxMyyK	M	10		32	#N/A	K	M
34	Side A		Side B		Base Part Number (xx-Length, yy-Breakout, p-Polarity)		Length in (M) (4 digit)		Breakout
35	MPO 12F (Female Low Loss)	S542LL-	MPO - 12F (Female Low Loss)	S542LL-	S542LL-BxxxxM	#N/A	0004		32	#N/A		M
Fiber Quick Quote Tool

Cell Formulas
Range	Formula
K31	K31	=IF(I31>=3,"32",IF(I31<3,"18",IF(I31="","")))
L31:L33	L31	=VLOOKUP(K31,'VLookup PN Generator'!$F$110:$G$112,2,FALSE)
K32:K33,K35	K32	=IF(I32>=3,"32",IF(I32<3,"18"))
O31	O31	=IFERROR(CONCATENATE(F31,I31,N31,K31,M31),"")
O32:O33	O32	=IFERROR(CONCATENATE(F32,I32,H32,L32,M32),"")
D35	D35	=F35
H35	H35	=IF(LEN(VLOOKUP(G35,'VLookup PN Generator'!$C$189:$D$190,2,0))=0,"",VLOOKUP(G35,'VLookup PN Generator'!$C$189:$D$190,2,0))
L35	L35	=VLOOKUP(K35,'VLookup PN Generator'!$C$178:$D$180,2,FALSE)
O35	O35	=IFERROR(CONCATENATE(F35,L35,I35,N35),"")

 

[Fluff]

I'm not saying you have to format it as text, I'm saying you need to change the formula I suggested depending on whether it should be 2 or 4 digits.

 

[Fluff]

The formula that kelliot suggested will not add the leading zeros. You have to do that in the formula as I showed.

 

[jtakw]

Hi,

What determines if it's 2 or 4 digits?

It will depend upon the item row. Its different for each part variation (one per row). Each row will have a set number of digits. These digits are the "length"

Since the 2 digit and 4 digit cells are Custom Formatted with leading 0 (zero) to fill the # of digits, perhaps if you Explain what rule(s) determine how you custom format the cell to either 2 or 4 digits will help in structuring a formula for your needs. Obviously, the cells didn't get the custom format by themselves, so your Must have something to go by.

 

[mjaeger1]

Try it like

=IFERROR(CONCATENATE(F31,TEXT(I31,"00"),N31,K31,M31),"")

@Fluff. Thank you! This ended up working out after some other minor changes. Thanks for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.