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),"")

 

[kelliott14]

hello,

you could swap the CONCATENATE for &s which will pull in the whole cell from column I.
So instead of =IFERROR(CONCATENATE(F31,I31,N31,K31,M31),"")
use =IFERROR(F31&I31&N31&K31&M31,"")

 

[mjaeger1]

hello,

you could swap the CONCATENATE for &s which will pull in the whole cell from column I.
So instead of =IFERROR(CONCATENATE(F31,I31,N31,K31,M31),"")
use =IFERROR(F31&I31&N31&K31&M31,"")

Thank you! Tried this and im getting the same result from the CONCAT function. It strips off the extra zeros and keeps only the number. Is there a formatting change that needs to made for this cell?

 

[Fluff]

Try it like

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

 

[mjaeger1]

Try it like

=IFERROR(CONCATENATE(F31,TEXT(I31,"00"),N31,K31,M31),"")[/CODE
[/QUOTE]

Try it like

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

Thanks for your reply! The only issue with that format is that the 4 digit format can be anything from 0001 - 0600, where the zeros are dynamic and will change depending on the inputted value. the correct amount of leading zeros is critical for a vlookup further down the sheet to pull an exact 'part number'

 

[Fluff]

What determines if it's 2 or 4 digits?

 

[mjaeger1]

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"

 

[Fluff]

If there is nothing in the data itself that dictates the number of digits, then you will just have to adjust the formula manually.

 

[mjaeger1]

Cell I35 is the data the CONCAT would be pulling from. I cannot have this cell (I35) formatted as text because the =IF formula in K35 depends on the custom format and wont work if its formatted as text.

 

[mjaeger1]

hello,

you could swap the CONCATENATE for &s which will pull in the whole cell from column I.
So instead of =IFERROR(CONCATENATE(F31,I31,N31,K31,M31),"")
use =IFERROR(F31&I31&N31&K31&M31,"")

Is there any other way to do it other than this?