3rd digit fixed input DV

[Zacheryryan93]

I'm trying to set up a sheet for part numbers and new to DV formats in excel.

the format I need should look like "##1###", Where the 3rd digit is a fixed number, and always 6 digits. I will have 9 columns where the 3rd digit is 1-9 in each. I'm capable enough to change the values between columns myself but having trouble getting the base format down. i have tried piecing together parts of similar formats with no success. I Know its not as complicated as it is being, I'm just unaccustomed to the logic of excel and haven't messed with any type of coding for 10+ years

Thanks in advance

 

[Zacheryryan93]

Edited column range G:R, as I am not using S, but yes I did change to 2000 in formula

 

[jtakw]

Edited column range G:R, as I am not using S, but yes I did change to 2000 in formula

Formula will need adjusted, Confirm one more time:

Book3.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	3rd digit must be>	1	2	5	5	3	3	6	6	6	6	6	4
Sheet1063

 

[Zacheryryan93]

Formula will need adjusted, Confirm one more time:

Book3.xlsx
	F	G	H	I	J	K	L	M	N	O	P	Q	R
2	3rd digit must be>	1	2	5	5	3	3	6	6	6	6	6	4
Sheet1063

Ahh there is an extra 6 in there. Column Q should be 4, Column R should be 8/9. Everything else is correct

 

[jtakw]

Try this:

=AND(LEN(G2000)=6,ISNUMBER(G2000+0),IF(COLUMN()=18,OR(MID(G2000,3,1)+0=8,MID(G2000,3,1)+0=9),MID(G2000,3,1)+0=CHOOSE(COLUMN()-6,1,2,5,5,3,3,6,6,6,6,4)))

 

[jtakw]

Just in case, All 6 characters in the cells are to be Numeric, with or without leading 0 (zero), correct ?
So No Alpha characters, like 0a1234, 123b56, etc. ?

 

[Zacheryryan93]

Try this:

=AND(LEN(G2000)=6,ISNUMBER(G2000+0),IF(COLUMN()=18,OR(MID(G2000,3,1)+0=8,MID(G2000,3,1)+0=9),MID(G2000,3,1)+0=CHOOSE(COLUMN()-6,1,2,5,5,3,3,6,6,6,6,4)))

It says code ends in error, but it works fine other than being able to start with zero! Tested all columns with same result. finally on the right path! You're awesome

 

[Zacheryryan93]

Just in case, All 6 characters in the cells are to be Numeric, with or without leading 0 (zero), correct ?
So No Alpha characters, like 0a1234, 123b56, etc. ?

Correct, Numeric only

 

[jtakw]

Don't know what you mean by "code ends in error", what error?

In order to Start with 0, you Have to Format all the cells as TEXT, before entering anything in the cells.
For example
Select G2000:R3000
Format cells
Text
click OK.

 

[Zacheryryan93]

Don't know what you mean by "code ends in error", what error?

In order to Start with 0, you Have to Format all the cells as TEXT, before entering anything in the cells.
For example
Select G2000:R3000
Format cells
Text
click OK.

First of all, I am an idiot! Hahahaha
I was using a test sheet and forgot to format for text. formatted for text and problem solved! you're a genius <3
When inputting the formula, it still states that the formula results in error, but when you okay to continue, it works just fine. Personally considering this issue resolved. I cant thank you enough!

 

[jtakw]

You're welcome, glad it's working for you, thanks for the feedback.

I'm puzzled about the error alert