If cells contains this, do this until...

[tblackwell]

Hi - Newbie here. Read the FAQs, rules and searched the forum and did not find an answer so created a new post. Bear with me.

Question: If cell's contains this do this until...
Hypothetical Example: Trying to write a formula to look for characters that begin with 6, then return the first four characters of that cell.
I got that but here is where I get stuck: If does not begin with 6, then use first four characters of cell that does begin with 6 UNTIL it finds a new number
Current formula where I get stuck: =IF(LEFT(C14,1)="6",LEFT(C14,4),C13)
System: Windows 10 Pro

	C	M	N	O	P	Q	R	S	T	U	V	W	X
13	Reference Cells							My Formula				What I Want
14	6001 24 Highway - 2192							6001				6001
15	L-FREE-CHIKNWFRYDRNK							6001 24 Highway - 2192				6001
16	L-FREE-SINGLE FRYDRK							L-FREE-CHIKNWFRYDRNK				6001
17	N-0200-OFF FULLSALAD							L-FREE-SINGLE FRYDRK				6001
18	N-0200-OFFCOMBO							N-0200-OFF FULLSALAD				6001
19	N-BOGO-3PC TENDERS							N-0200-OFFCOMBO				6001
20	N-BOGO-CHICKEN							N-BOGO-3PC TENDERS				6001
21	N-BOGO-SINGLECHEESE							N-BOGO-CHICKEN				6001
22	N-DISC-2SGLCHZ CMB							N-BOGO-SINGLECHEESE				6001
23	N-DISC-2SPC CHK CMB							N-DISC-2SGLCHZ CMB				6001
24	N-DISC-DBL CHZ CMB							N-DISC-2SPC CHK CMB				6001
25	N-DISC-KM CHICKEN							N-DISC-DBL CHZ CMB				6001
26	N-DISC-KM CHK TENDER							N-DISC-KM CHICKEN				6001
27	N-DISC-KM CHZBURGER							N-DISC-KM CHK TENDER				6001
28	N-DISC-KM HAMBURGER							N-DISC-KM CHZBURGER				6001
29	N-DISC-KM NUGGET							N-DISC-KM HAMBURGER				6001
30	N-DISC-KM WRAPS							N-DISC-KM NUGGET				6001
31	N-FREE-COOKIE							N-DISC-KM WRAPS				6001
32	N-FREE-JRFRSTYKEYTAG							N-FREE-COOKIE				6001
33	N-FREE-TNDRSW/FRYDRK							N-FREE-JRFRSTYKEYTAG				6001
34	6001 24 Highway - 2192 Quantity:							6001				6001
35	6002 40 Highway - 8246							6002				6002
36	L-FREE-CHIKNWFRYDRNK							6002 40 Highway - 8246				6002
37	L-FREE-SINGLE FRYDRK							L-FREE-CHIKNWFRYDRNK				6002
38	N-0200-OFF FULLSALAD							L-FREE-SINGLE FRYDRK				6002
39	N-0200-OFFCOMBO							N-0200-OFF FULLSALAD				6002
40	N-BOGO-3PC TENDERS							N-0200-OFFCOMBO				6002
41	N-BOGO-CHICKEN							N-BOGO-3PC TENDERS				6002
42	N-BOGO-SINGLECHEESE							N-BOGO-CHICKEN				6002
43	N-DISC-2SGLCHZ CMB							N-BOGO-SINGLECHEESE				6002
44	N-DISC-2SPC CHK CMB							N-DISC-2SGLCHZ CMB				6002
45	N-DISC-DBL CHZ CMB							N-DISC-2SPC CHK CMB				6002
46	N-DISC-KM CHICKEN							N-DISC-DBL CHZ CMB				6002
47	N-DISC-KM CHK TENDER							N-DISC-KM CHICKEN				6002
48	N-DISC-KM CHZBURGER							N-DISC-KM CHK TENDER				6002
49	N-DISC-KM HAMBURGER							N-DISC-KM CHZBURGER				6002
50	N-DISC-KM NUGGET							N-DISC-KM HAMBURGER				6002
51	N-DISC-KM WRAPS							N-DISC-KM NUGGET				6002
52	N-FREE-COOKIE							N-DISC-KM WRAPS				6002
53	N-FREE-JRFRSTYKEYTAG							N-FREE-COOKIE				6002
54	N-FREE-TNDRSW/FRYDRK							N-FREE-JRFRSTYKEYTAG				6002
55	6002 40 Highway - 8246 Quantity:							6002				6002
56	6003 State Ave - 2183							6003				6003
57	L-FREE-CHIKNWFRYDRNK							6003 State Ave - 2183				6003
58	L-FREE-SINGLE FRYDRK							L-FREE-CHIKNWFRYDRNK				6003

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

ToFormat​

Worksheet Formulas Cell Formula C14 =IF('[Promotion Detail_twb.xls]Sheet1'!A10="",'[Promotion Detail_twb.xls]Sheet1'!B10,'[Promotion Detail_twb.xls]Sheet1'!A10) C15 =IF('[Promotion Detail_twb.xls]Sheet1'!A11="",'[Promotion Detail_twb.xls]Sheet1'!B11,'[Promotion Detail_twb.xls]Sheet1'!A11) C16 =IF('[Promotion Detail_twb.xls]Sheet1'!A12="",'[Promotion Detail_twb.xls]Sheet1'!B12,'[Promotion Detail_twb.xls]Sheet1'!A12) C17 =IF('[Promotion Detail_twb.xls]Sheet1'!A13="",'[Promotion Detail_twb.xls]Sheet1'!B13,'[Promotion Detail_twb.xls]Sheet1'!A13) C18 =IF('[Promotion Detail_twb.xls]Sheet1'!A14="",'[Promotion Detail_twb.xls]Sheet1'!B14,'[Promotion Detail_twb.xls]Sheet1'!A14) C19 =IF('[Promotion Detail_twb.xls]Sheet1'!A15="",'[Promotion Detail_twb.xls]Sheet1'!B15,'[Promotion Detail_twb.xls]Sheet1'!A15) C20 =IF('[Promotion Detail_twb.xls]Sheet1'!A16="",'[Promotion Detail_twb.xls]Sheet1'!B16,'[Promotion Detail_twb.xls]Sheet1'!A16) C21 =IF('[Promotion Detail_twb.xls]Sheet1'!A17="",'[Promotion Detail_twb.xls]Sheet1'!B17,'[Promotion Detail_twb.xls]Sheet1'!A17) C22 =IF('[Promotion Detail_twb.xls]Sheet1'!A18="",'[Promotion Detail_twb.xls]Sheet1'!B18,'[Promotion Detail_twb.xls]Sheet1'!A18) C23 =IF('[Promotion Detail_twb.xls]Sheet1'!A19="",'[Promotion Detail_twb.xls]Sheet1'!B19,'[Promotion Detail_twb.xls]Sheet1'!A19) C24 =IF('[Promotion Detail_twb.xls]Sheet1'!A20="",'[Promotion Detail_twb.xls]Sheet1'!B20,'[Promotion Detail_twb.xls]Sheet1'!A20) C25 =IF('[Promotion Detail_twb.xls]Sheet1'!A21="",'[Promotion Detail_twb.xls]Sheet1'!B21,'[Promotion Detail_twb.xls]Sheet1'!A21) C26 =IF('[Promotion Detail_twb.xls]Sheet1'!A22="",'[Promotion Detail_twb.xls]Sheet1'!B22,'[Promotion Detail_twb.xls]Sheet1'!A22) C27 =IF('[Promotion Detail_twb.xls]Sheet1'!A23="",'[Promotion Detail_twb.xls]Sheet1'!B23,'[Promotion Detail_twb.xls]Sheet1'!A23) C28 =IF('[Promotion Detail_twb.xls]Sheet1'!A24="",'[Promotion Detail_twb.xls]Sheet1'!B24,'[Promotion Detail_twb.xls]Sheet1'!A24) C29 =IF('[Promotion Detail_twb.xls]Sheet1'!A25="",'[Promotion Detail_twb.xls]Sheet1'!B25,'[Promotion Detail_twb.xls]Sheet1'!A25) C30 =IF('[Promotion Detail_twb.xls]Sheet1'!A26="",'[Promotion Detail_twb.xls]Sheet1'!B26,'[Promotion Detail_twb.xls]Sheet1'!A26) C31 =IF('[Promotion Detail_twb.xls]Sheet1'!A27="",'[Promotion Detail_twb.xls]Sheet1'!B27,'[Promotion Detail_twb.xls]Sheet1'!A27) C32 =IF('[Promotion Detail_twb.xls]Sheet1'!A28="",'[Promotion Detail_twb.xls]Sheet1'!B28,'[Promotion Detail_twb.xls]Sheet1'!A28) C33 =IF('[Promotion Detail_twb.xls]Sheet1'!A29="",'[Promotion Detail_twb.xls]Sheet1'!B29,'[Promotion Detail_twb.xls]Sheet1'!A29) C34 =IF('[Promotion Detail_twb.xls]Sheet1'!A30="",'[Promotion Detail_twb.xls]Sheet1'!B30,'[Promotion Detail_twb.xls]Sheet1'!A30) C35 =IF('[Promotion Detail_twb.xls]Sheet1'!A31="",'[Promotion Detail_twb.xls]Sheet1'!B31,'[Promotion Detail_twb.xls]Sheet1'!A31) C36 =IF('[Promotion Detail_twb.xls]Sheet1'!A32="",'[Promotion Detail_twb.xls]Sheet1'!B32,'[Promotion Detail_twb.xls]Sheet1'!A32) C37 =IF('[Promotion Detail_twb.xls]Sheet1'!A33="",'[Promotion Detail_twb.xls]Sheet1'!B33,'[Promotion Detail_twb.xls]Sheet1'!A33) C38 =IF('[Promotion Detail_twb.xls]Sheet1'!A34="",'[Promotion Detail_twb.xls]Sheet1'!B34,'[Promotion Detail_twb.xls]Sheet1'!A34) C39 =IF('[Promotion Detail_twb.xls]Sheet1'!A35="",'[Promotion Detail_twb.xls]Sheet1'!B35,'[Promotion Detail_twb.xls]Sheet1'!A35) C40 =IF('[Promotion Detail_twb.xls]Sheet1'!A36="",'[Promotion Detail_twb.xls]Sheet1'!B36,'[Promotion Detail_twb.xls]Sheet1'!A36) C41 =IF('[Promotion Detail_twb.xls]Sheet1'!A37="",'[Promotion Detail_twb.xls]Sheet1'!B37,'[Promotion Detail_twb.xls]Sheet1'!A37) C42 =IF('[Promotion Detail_twb.xls]Sheet1'!A38="",'[Promotion Detail_twb.xls]Sheet1'!B38,'[Promotion Detail_twb.xls]Sheet1'!A38) C43 =IF('[Promotion Detail_twb.xls]Sheet1'!A39="",'[Promotion Detail_twb.xls]Sheet1'!B39,'[Promotion Detail_twb.xls]Sheet1'!A39) C44 =IF('[Promotion Detail_twb.xls]Sheet1'!A40="",'[Promotion Detail_twb.xls]Sheet1'!B40,'[Promotion Detail_twb.xls]Sheet1'!A40) C45 =IF('[Promotion Detail_twb.xls]Sheet1'!A41="",'[Promotion Detail_twb.xls]Sheet1'!B41,'[Promotion Detail_twb.xls]Sheet1'!A41) C46 =IF('[Promotion Detail_twb.xls]Sheet1'!A42="",'[Promotion Detail_twb.xls]Sheet1'!B42,'[Promotion Detail_twb.xls]Sheet1'!A42) C47 =IF('[Promotion Detail_twb.xls]Sheet1'!A43="",'[Promotion Detail_twb.xls]Sheet1'!B43,'[Promotion Detail_twb.xls]Sheet1'!A43) C48 =IF('[Promotion Detail_twb.xls]Sheet1'!A44="",'[Promotion Detail_twb.xls]Sheet1'!B44,'[Promotion Detail_twb.xls]Sheet1'!A44) C49 =IF('[Promotion Detail_twb.xls]Sheet1'!A45="",'[Promotion Detail_twb.xls]Sheet1'!B45,'[Promotion Detail_twb.xls]Sheet1'!A45) C50 =IF('[Promotion Detail_twb.xls]Sheet1'!A46="",'[Promotion Detail_twb.xls]Sheet1'!B46,'[Promotion Detail_twb.xls]Sheet1'!A46) C51 =IF('[Promotion Detail_twb.xls]Sheet1'!A47="",'[Promotion Detail_twb.xls]Sheet1'!B47,'[Promotion Detail_twb.xls]Sheet1'!A47) C52 =IF('[Promotion Detail_twb.xls]Sheet1'!A48="",'[Promotion Detail_twb.xls]Sheet1'!B48,'[Promotion Detail_twb.xls]Sheet1'!A48) C53 =IF('[Promotion Detail_twb.xls]Sheet1'!A49="",'[Promotion Detail_twb.xls]Sheet1'!B49,'[Promotion Detail_twb.xls]Sheet1'!A49) C54 =IF('[Promotion Detail_twb.xls]Sheet1'!A50="",'[Promotion Detail_twb.xls]Sheet1'!B50,'[Promotion Detail_twb.xls]Sheet1'!A50) C55 =IF('[Promotion Detail_twb.xls]Sheet1'!A51="",'[Promotion Detail_twb.xls]Sheet1'!B51,'[Promotion Detail_twb.xls]Sheet1'!A51) C56 =IF('[Promotion Detail_twb.xls]Sheet1'!A52="",'[Promotion Detail_twb.xls]Sheet1'!B52,'[Promotion Detail_twb.xls]Sheet1'!A52) C57 =IF('[Promotion Detail_twb.xls]Sheet1'!A53="",'[Promotion Detail_twb.xls]Sheet1'!B53,'[Promotion Detail_twb.xls]Sheet1'!A53) C58 =IF('[Promotion Detail_twb.xls]Sheet1'!A54="",'[Promotion Detail_twb.xls]Sheet1'!B54,'[Promotion Detail_twb.xls]Sheet1'!A54) C59 =IF('[Promotion Detail_twb.xls]Sheet1'!A55="",'[Promotion Detail_twb.xls]Sheet1'!B55,'[Promotion Detail_twb.xls]Sheet1'!A55) C60 =IF('[Promotion Detail_twb.xls]Sheet1'!A56="",'[Promotion Detail_twb.xls]Sheet1'!B56,'[Promotion Detail_twb.xls]Sheet1'!A56) C61 =IF('[Promotion Detail_twb.xls]Sheet1'!A57="",'[Promotion Detail_twb.xls]Sheet1'!B57,'[Promotion Detail_twb.xls]Sheet1'!A57) C62 =IF('[Promotion Detail_twb.xls]Sheet1'!A58="",'[Promotion Detail_twb.xls]Sheet1'!B58,'[Promotion Detail_twb.xls]Sheet1'!A58) C63 =IF('[Promotion Detail_twb.xls]Sheet1'!A59="",'[Promotion Detail_twb.xls]Sheet1'!B59,'[Promotion Detail_twb.xls]Sheet1'!A59) C64 =IF('[Promotion Detail_twb.xls]Sheet1'!A60="",'[Promotion Detail_twb.xls]Sheet1'!B60,'[Promotion Detail_twb.xls]Sheet1'!A60) C65 =IF('[Promotion Detail_twb.xls]Sheet1'!A61="",'[Promotion Detail_twb.xls]Sheet1'!B61,'[Promotion Detail_twb.xls]Sheet1'!A61) C66 =IF('[Promotion Detail_twb.xls]Sheet1'!A62="",'[Promotion Detail_twb.xls]Sheet1'!B62,'[Promotion Detail_twb.xls]Sheet1'!A62) C67 =IF('[Promotion Detail_twb.xls]Sheet1'!A63="",'[Promotion Detail_twb.xls]Sheet1'!B63,'[Promotion Detail_twb.xls]Sheet1'!A63) C68 =IF('[Promotion Detail_twb.xls]Sheet1'!A64="",'[Promotion Detail_twb.xls]Sheet1'!B64,'[Promotion Detail_twb.xls]Sheet1'!A64) C69 =IF('[Promotion Detail_twb.xls]Sheet1'!A65="",'[Promotion Detail_twb.xls]Sheet1'!B65,'[Promotion Detail_twb.xls]Sheet1'!A65) C70 =IF('[Promotion Detail_twb.xls]Sheet1'!A66="",'[Promotion Detail_twb.xls]Sheet1'!B66,'[Promotion Detail_twb.xls]Sheet1'!A66) S14 =IF(LEFT(C14,1)="6",LEFT(C14,4),C13) S15 =IF(LEFT(C15,1)="6",LEFT(C15,4),C14) S16 =IF(LEFT(C16,1)="6",LEFT(C16,4),C15) S17 =IF(LEFT(C17,1)="6",LEFT(C17,4),C16) S18 =IF(LEFT(C18,1)="6",LEFT(C18,4),C17) S19 =IF(LEFT(C19,1)="6",LEFT(C19,4),C18) S20 =IF(LEFT(C20,1)="6",LEFT(C20,4),C19) S21 =IF(LEFT(C21,1)="6",LEFT(C21,4),C20) S22 =IF(LEFT(C22,1)="6",LEFT(C22,4),C21) S23 =IF(LEFT(C23,1)="6",LEFT(C23,4),C22) S24 =IF(LEFT(C24,1)="6",LEFT(C24,4),C23) S25 =IF(LEFT(C25,1)="6",LEFT(C25,4),C24) S26 =IF(LEFT(C26,1)="6",LEFT(C26,4),C25) S27 =IF(LEFT(C27,1)="6",LEFT(C27,4),C26) S28 =IF(LEFT(C28,1)="6",LEFT(C28,4),C27) S29 =IF(LEFT(C29,1)="6",LEFT(C29,4),C28) S30 =IF(LEFT(C30,1)="6",LEFT(C30,4),C29) S31 =IF(LEFT(C31,1)="6",LEFT(C31,4),C30) S32 =IF(LEFT(C32,1)="6",LEFT(C32,4),C31) S33 =IF(LEFT(C33,1)="6",LEFT(C33,4),C32) S34 =IF(LEFT(C34,1)="6",LEFT(C34,4),C33) S35 =IF(LEFT(C35,1)="6",LEFT(C35,4),C34) S36 =IF(LEFT(C36,1)="6",LEFT(C36,4),C35) S37 =IF(LEFT(C37,1)="6",LEFT(C37,4),C36) S38 =IF(LEFT(C38,1)="6",LEFT(C38,4),C37) S39 =IF(LEFT(C39,1)="6",LEFT(C39,4),C38) S40 =IF(LEFT(C40,1)="6",LEFT(C40,4),C39) S41 =IF(LEFT(C41,1)="6",LEFT(C41,4),C40) S42 =IF(LEFT(C42,1)="6",LEFT(C42,4),C41) S43 =IF(LEFT(C43,1)="6",LEFT(C43,4),C42) S44 =IF(LEFT(C44,1)="6",LEFT(C44,4),C43) S45 =IF(LEFT(C45,1)="6",LEFT(C45,4),C44) S46 =IF(LEFT(C46,1)="6",LEFT(C46,4),C45) S47 =IF(LEFT(C47,1)="6",LEFT(C47,4),C46) S48 =IF(LEFT(C48,1)="6",LEFT(C48,4),C47) S49 =IF(LEFT(C49,1)="6",LEFT(C49,4),C48) S50 =IF(LEFT(C50,1)="6",LEFT(C50,4),C49) S51 =IF(LEFT(C51,1)="6",LEFT(C51,4),C50) S52 =IF(LEFT(C52,1)="6",LEFT(C52,4),C51) S53 =IF(LEFT(C53,1)="6",LEFT(C53,4),C52) S54 =IF(LEFT(C54,1)="6",LEFT(C54,4),C53) S55 =IF(LEFT(C55,1)="6",LEFT(C55,4),C54) S56 =IF(LEFT(C56,1)="6",LEFT(C56,4),C55) S57 =IF(LEFT(C57,1)="6",LEFT(C57,4),C56) S58 =IF(LEFT(C58,1)="6",LEFT(C58,4),C57) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Scott Huish]

Re: If cell's contains this, do this until...

Formula in S14:
=IF(ISNUMBER(LEFT(C14,4)+0),LEFT(C14,4),S13)

Copy down.

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
13	Reference Cells																What I Want
14	6001 24 Highway - 2192																6001
15	L-FREE-CHIKNWFRYDRNK																6001
16	L-FREE-SINGLE FRYDRK																6001
17	N-0200-OFF FULLSALAD																6001
18	N-0200-OFFCOMBO																6001
19	N-BOGO-3PC TENDERS																6001
20	N-BOGO-CHICKEN																6001
21	N-BOGO-SINGLECHEESE																6001
22	N-DISC-2SGLCHZ CMB																6001
23	N-DISC-2SPC CHK CMB																6001
24	N-DISC-DBL CHZ CMB																6001
25	N-DISC-KM CHICKEN																6001
26	N-DISC-KM CHK TENDER																6001
27	N-DISC-KM CHZBURGER																6001
28	N-DISC-KM HAMBURGER																6001
29	N-DISC-KM NUGGET																6001
30	N-DISC-KM WRAPS																6001
31	N-FREE-COOKIE																6001
32	N-FREE-JRFRSTYKEYTAG																6001
33	N-FREE-TNDRSW/FRYDRK																6001
34	6001 24 Highway - 2192 Quantity:																6001
35	6002 40 Highway - 8246																6002
36	L-FREE-CHIKNWFRYDRNK																6002
37	L-FREE-SINGLE FRYDRK																6002
38	N-0200-OFF FULLSALAD																6002
39	N-0200-OFFCOMBO																6002
40	N-BOGO-3PC TENDERS																6002
41	N-BOGO-CHICKEN																6002
42	N-BOGO-SINGLECHEESE																6002
43	N-DISC-2SGLCHZ CMB																6002
44	N-DISC-2SPC CHK CMB																6002
45	N-DISC-DBL CHZ CMB																6002
46	N-DISC-KM CHICKEN																6002
47	N-DISC-KM CHK TENDER																6002
48	N-DISC-KM CHZBURGER																6002
49	N-DISC-KM HAMBURGER																6002
50	N-DISC-KM NUGGET																6002
51	N-DISC-KM WRAPS																6002
52	N-FREE-COOKIE																6002
53	N-FREE-JRFRSTYKEYTAG																6002
54	N-FREE-TNDRSW/FRYDRK																6002
55	6002 40 Highway - 8246 Quantity:																6002
56	6003 State Ave - 2183																6003
57	L-FREE-CHIKNWFRYDRNK																6003
58	L-FREE-SINGLE FRYDRK																6003
Sheet1

Cell Formulas
Range	Formula
S14	=IF(ISNUMBER(LEFT(C14,4)+0),LEFT(C14,4),S13)
S15	=IF(ISNUMBER(LEFT(C15,4)+0),LEFT(C15,4),S14)
S16	=IF(ISNUMBER(LEFT(C16,4)+0),LEFT(C16,4),S15)
S17	=IF(ISNUMBER(LEFT(C17,4)+0),LEFT(C17,4),S16)
S18	=IF(ISNUMBER(LEFT(C18,4)+0),LEFT(C18,4),S17)
S19	=IF(ISNUMBER(LEFT(C19,4)+0),LEFT(C19,4),S18)
S20	=IF(ISNUMBER(LEFT(C20,4)+0),LEFT(C20,4),S19)
S21	=IF(ISNUMBER(LEFT(C21,4)+0),LEFT(C21,4),S20)
S22	=IF(ISNUMBER(LEFT(C22,4)+0),LEFT(C22,4),S21)
S23	=IF(ISNUMBER(LEFT(C23,4)+0),LEFT(C23,4),S22)
S24	=IF(ISNUMBER(LEFT(C24,4)+0),LEFT(C24,4),S23)
S25	=IF(ISNUMBER(LEFT(C25,4)+0),LEFT(C25,4),S24)
S26	=IF(ISNUMBER(LEFT(C26,4)+0),LEFT(C26,4),S25)
S27	=IF(ISNUMBER(LEFT(C27,4)+0),LEFT(C27,4),S26)
S28	=IF(ISNUMBER(LEFT(C28,4)+0),LEFT(C28,4),S27)
S29	=IF(ISNUMBER(LEFT(C29,4)+0),LEFT(C29,4),S28)
S30	=IF(ISNUMBER(LEFT(C30,4)+0),LEFT(C30,4),S29)
S31	=IF(ISNUMBER(LEFT(C31,4)+0),LEFT(C31,4),S30)
S32	=IF(ISNUMBER(LEFT(C32,4)+0),LEFT(C32,4),S31)
S33	=IF(ISNUMBER(LEFT(C33,4)+0),LEFT(C33,4),S32)
S34	=IF(ISNUMBER(LEFT(C34,4)+0),LEFT(C34,4),S33)
S35	=IF(ISNUMBER(LEFT(C35,4)+0),LEFT(C35,4),S34)
S36	=IF(ISNUMBER(LEFT(C36,4)+0),LEFT(C36,4),S35)
S37	=IF(ISNUMBER(LEFT(C37,4)+0),LEFT(C37,4),S36)
S38	=IF(ISNUMBER(LEFT(C38,4)+0),LEFT(C38,4),S37)
S39	=IF(ISNUMBER(LEFT(C39,4)+0),LEFT(C39,4),S38)
S40	=IF(ISNUMBER(LEFT(C40,4)+0),LEFT(C40,4),S39)
S41	=IF(ISNUMBER(LEFT(C41,4)+0),LEFT(C41,4),S40)
S42	=IF(ISNUMBER(LEFT(C42,4)+0),LEFT(C42,4),S41)
S43	=IF(ISNUMBER(LEFT(C43,4)+0),LEFT(C43,4),S42)
S44	=IF(ISNUMBER(LEFT(C44,4)+0),LEFT(C44,4),S43)
S45	=IF(ISNUMBER(LEFT(C45,4)+0),LEFT(C45,4),S44)
S46	=IF(ISNUMBER(LEFT(C46,4)+0),LEFT(C46,4),S45)
S47	=IF(ISNUMBER(LEFT(C47,4)+0),LEFT(C47,4),S46)
S48	=IF(ISNUMBER(LEFT(C48,4)+0),LEFT(C48,4),S47)
S49	=IF(ISNUMBER(LEFT(C49,4)+0),LEFT(C49,4),S48)
S50	=IF(ISNUMBER(LEFT(C50,4)+0),LEFT(C50,4),S49)
S51	=IF(ISNUMBER(LEFT(C51,4)+0),LEFT(C51,4),S50)
S52	=IF(ISNUMBER(LEFT(C52,4)+0),LEFT(C52,4),S51)
S53	=IF(ISNUMBER(LEFT(C53,4)+0),LEFT(C53,4),S52)
S54	=IF(ISNUMBER(LEFT(C54,4)+0),LEFT(C54,4),S53)
S55	=IF(ISNUMBER(LEFT(C55,4)+0),LEFT(C55,4),S54)
S56	=IF(ISNUMBER(LEFT(C56,4)+0),LEFT(C56,4),S55)
S57	=IF(ISNUMBER(LEFT(C57,4)+0),LEFT(C57,4),S56)
S58	=IF(ISNUMBER(LEFT(C58,4)+0),LEFT(C58,4),S57)

 

[MARK858]

Re: If cell's contains this, do this until...

Can't you just do.....

Excel Workbook
	C	D	E	S
13	Reference Cells			My Formula
14	6001 24 Highway - 2192			6001
15	L-FREE-CHIKNWFRYDRNK			6001
16	L-FREE-SINGLE FRYDRK			6001
17	N-0200-OFF FULLSALAD			6001
18	N-0200-OFFCOMBO			6001
19	N-BOGO-3PC TENDERS			6001
20	N-BOGO-CHICKEN			6001
21	N-BOGO-SINGLECHEESE			6001
22	N-DISC-2SGLCHZ CMB			6001
23	N-DISC-2SPC CHK CMB			6001
24	N-DISC-DBL CHZ CMB			6001
25	N-DISC-KM CHICKEN			6001
26	N-DISC-KM CHK TENDER			6001
27	N-DISC-KM CHZBURGER			6001
28	N-DISC-KM HAMBURGER			6001
29	N-DISC-KM NUGGET			6001
30	N-DISC-KM WRAPS			6001
31	N-FREE-COOKIE			6001
32	N-FREE-JRFRSTYKEYTAG			6001
33	N-FREE-TNDRSW/FRYDRK			6001
34	6001 24 Highway - 2192 Quantity:			6001
35	6002 40 Highway - 8246			6002
36	L-FREE-CHIKNWFRYDRNK			6002
37	L-FREE-SINGLE FRYDRK			6002
38	N-0200-OFF FULLSALAD			6002
39	N-0200-OFFCOMBO			6002
40	N-BOGO-3PC TENDERS			6002
41	N-BOGO-CHICKEN			6002
42	N-BOGO-SINGLECHEESE			6002
43	N-DISC-2SGLCHZ CMB			6002
44	N-DISC-2SPC CHK CMB			6002
45	N-DISC-DBL CHZ CMB			6002
46	N-DISC-KM CHICKEN			6002
47	N-DISC-KM CHK TENDER			6002
48	N-DISC-KM CHZBURGER			6002
49	N-DISC-KM HAMBURGER			6002
50	N-DISC-KM NUGGET			6002
51	N-DISC-KM WRAPS			6002
52	N-FREE-COOKIE			6002
53	N-FREE-JRFRSTYKEYTAG			6002
54	N-FREE-TNDRSW/FRYDRK			6002
55	6002 40 Highway - 8246 Quantity:			6002
56	6003 State Ave - 2183			6003
57	L-FREE-CHIKNWFRYDRNK			6003
58	L-FREE-SINGLE FRYDRK			6003
Sheet2

 

[jtakw]

Re: If cell's contains this, do this until...

Hi,

Book1
	C	D	W
13	Reference Cells		What I Want
14	6001 24 Highway - 2192		6001
15	L-FREE-CHIKNWFRYDRNK		6001
16	L-FREE-SINGLE FRYDRK		6001
17	N-0200-OFF FULLSALAD		6001
18	N-0200-OFFCOMBO		6001
19	N-BOGO-3PC TENDERS		6001
20	N-BOGO-CHICKEN		6001
21	N-BOGO-SINGLECHEESE		6001
22	N-DISC-2SGLCHZ CMB		6001
23	N-DISC-2SPC CHK CMB		6001
24	N-DISC-DBL CHZ CMB		6001
25	N-DISC-KM CHICKEN		6001
26	N-DISC-KM CHK TENDER		6001
27	N-DISC-KM CHZBURGER		6001
28	N-DISC-KM HAMBURGER		6001
29	N-DISC-KM NUGGET		6001
30	N-DISC-KM WRAPS		6001
31	N-FREE-COOKIE		6001
32	N-FREE-JRFRSTYKEYTAG		6001
33	N-FREE-TNDRSW/FRYDRK		6001
34	6001 24 Highway - 2192 Quantity:		6001
35	6002 40 Highway - 8246		6002
36	L-FREE-CHIKNWFRYDRNK		6002
37	L-FREE-SINGLE FRYDRK		6002
38	N-0200-OFF FULLSALAD		6002
39	N-0200-OFFCOMBO		6002
40	N-BOGO-3PC TENDERS		6002
41	N-BOGO-CHICKEN		6002
42	N-BOGO-SINGLECHEESE		6002
43	N-DISC-2SGLCHZ CMB		6002
44	N-DISC-2SPC CHK CMB		6002
45	N-DISC-DBL CHZ CMB		6002
46	N-DISC-KM CHICKEN		6002
47	N-DISC-KM CHK TENDER		6002
48	N-DISC-KM CHZBURGER		6002
49	N-DISC-KM HAMBURGER		6002
50	N-DISC-KM NUGGET		6002
51	N-DISC-KM WRAPS		6002
52	N-FREE-COOKIE		6002
53	N-FREE-JRFRSTYKEYTAG		6002
54	N-FREE-TNDRSW/FRYDRK		6002
55	6002 40 Highway - 8246 Quantity:		6002
56	6003 State Ave - 2183		6003
57	L-FREE-CHIKNWFRYDRNK		6003
58	L-FREE-SINGLE FRYDRK		6003
Sheet345

Cell Formulas
Range	Formula
W14	=IF(LEFT(C14)="6",LEFT(C14,4)+0,W13)

Formula copied down.

 

[Kamolga]

Re: If cell's contains this, do this until...

You can put this is S14 and dragg it down

=IF(ISNUMBER(VALUE(LEFT(C14,4))),VALUE(LEFT(C14,4)),S13)

 

[MARK858]

Re: If cell's contains this, do this until...

Trying to write a formula to look for characters that begin with 6

Maybe I am reading it wrong but a couple of posts are seeming to ignore that the question specifically states characters that begin with a 6

 

[Kamolga]

Re: If cell's contains this, do this until...

=if(and(isnumber(value(left(c14,4))),left(c14,1)="6"),value(left(c15,4)),s13)

 

[tblackwell]

Re: If cell's contains this, do this until...

Thanks everyone! Who knew there could be so many different answers for the same problem? Looks like everyone's solution worked and I would like to give a special shout out to Scott Huish and Kamolga who went one step further and made this formula useful beyond the original request of using the number 6. Thanks again and one last thing - if there was any way I can make my future posts better, be sure to let me know!

	C	Q	R	S	T	U	V	W	X
13	Reference Cells			Scott H	MARK858	jtakw	Kamolga		What I Want
14	6001 24 Highway - 2192			6001	6001	6001	6001		6001
15	L-FREE-CHIKNWFRYDRNK			6001	6001	6001	6001		6001
16	L-FREE-SINGLE FRYDRK			6001	6001	6001	6001		6001
17	N-0200-OFF FULLSALAD			6001	6001	6001	6001		6001
18	N-0200-OFFCOMBO			6001	6001	6001	6001		6001
19	N-BOGO-3PC TENDERS			6001	6001	6001	6001		6001
20	N-BOGO-CHICKEN			6001	6001	6001	6001		6001
21	N-BOGO-SINGLECHEESE			6001	6001	6001	6001		6001
22	N-DISC-2SGLCHZ CMB			6001	6001	6001	6001		6001
23	N-DISC-2SPC CHK CMB			6001	6001	6001	6001		6001
24	N-DISC-DBL CHZ CMB			6001	6001	6001	6001		6001
25	N-DISC-KM CHICKEN			6001	6001	6001	6001		6001
26	N-DISC-KM CHK TENDER			6001	6001	6001	6001		6001
27	N-DISC-KM CHZBURGER			6001	6001	6001	6001		6001
28	N-DISC-KM HAMBURGER			6001	6001	6001	6001		6001
29	N-DISC-KM NUGGET			6001	6001	6001	6001		6001
30	N-DISC-KM WRAPS			6001	6001	6001	6001		6001
31	N-FREE-COOKIE			6001	6001	6001	6001		6001
32	N-FREE-JRFRSTYKEYTAG			6001	6001	6001	6001		6001
33	N-FREE-TNDRSW/FRYDRK			6001	6001	6001	6001		6001
34	6001 24 Highway - 2192 Quantity:			6001	6001	6001	6001		6001
35	6002 40 Highway - 8246			6002	6002	6002	6002		6002
36	L-FREE-CHIKNWFRYDRNK			6002	6002	6002	6002		6002
37	L-FREE-SINGLE FRYDRK			6002	6002	6002	6002		6002
38	N-0200-OFF FULLSALAD			6002	6002	6002	6002		6002
39	N-0200-OFFCOMBO			6002	6002	6002	6002		6002
40	N-BOGO-3PC TENDERS			6002	6002	6002	6002		6002
41	N-BOGO-CHICKEN			6002	6002	6002	6002		6002
42	N-BOGO-SINGLECHEESE			6002	6002	6002	6002		6002
43	N-DISC-2SGLCHZ CMB			6002	6002	6002	6002		6002
44	N-DISC-2SPC CHK CMB			6002	6002	6002	6002		6002
45	N-DISC-DBL CHZ CMB			6002	6002	6002	6002		6002
46	N-DISC-KM CHICKEN			6002	6002	6002	6002		6002
47	N-DISC-KM CHK TENDER			6002	6002	6002	6002		6002
48	N-DISC-KM CHZBURGER			6002	6002	6002	6002		6002
49	N-DISC-KM HAMBURGER			6002	6002	6002	6002		6002
50	N-DISC-KM NUGGET			6002	6002	6002	6002		6002
51	N-DISC-KM WRAPS			6002	6002	6002	6002		6002
52	N-FREE-COOKIE			6002	6002	6002	6002		6002
53	N-FREE-JRFRSTYKEYTAG			6002	6002	6002	6002		6002
54	N-FREE-TNDRSW/FRYDRK			6002	6002	6002	6002		6002
55	6002 40 Highway - 8246 Quantity:			6002	6002	6002	6002		6002
56	6003 State Ave - 2183			6003	6003	6003	6003		6003
57	L-FREE-CHIKNWFRYDRNK			6003	6003	6003	6003		6003
58	L-FREE-SINGLE FRYDRK			6003	6003	6003	6003		6003
59	N-0200-OFF FULLSALAD			6003	6003	6003	6003		6003
60	N-0200-OFFCOMBO			6003	6003	6003	6003		6003
61	N-BOGO-3PC TENDERS			6003	6003	6003	6003		6003
62	N-BOGO-CHICKEN			6003	6003	6003	6003		6003

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

ToFormat​

 

[tblackwell]

Re: If cell's contains this, do this until...

Hi Everyone - Me again! This formula served me well for months until I ran into the following problem:

Formula: The formula in column S was created to return store numbers. All my store numbers begin with 6. Example: Cell A194 is store number 6001.
What you should know: Dates are also in column A
Which Means: The formula works great until you get to the month of June, which also begins with a 6.
Actually: The formula still works with a June date until 6/10/2018???
Desired Result: Formula in column S that will continue to deliver store numbers instead of the date from column A

Can you help?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
194	6001 Highway - 2192																		6001
195		5/24/2018																$3,516.49	6001
196							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
197	5/24/2018 Total:						0					$0.00			$0.00			0.00%	6001
198		5/25/2018																$4,137.76	6001
199			Coupon 1				2		$3.19			$6.38			$6.40	100.00%		0.15%	6001
200	5/25/2018 Total:						2					$6.38			$6.40			0.15%	6001
201		5/26/2018																$3,612.60	6001
202			Coupon 1				1		$0.00			$0.00			$2.59	100.00%		0.00%	6001
203	5/26/2018 Total:						1					$0.00			$2.59			0.00%	6001
204		5/27/2018																$3,121.88	6001
205			Coupon 1				1		$3.19			$3.19			$3.20	100.00%		0.10%	6001
206	5/27/2018 Total:						1					$3.19			$3.20			0.10%	6001
207		5/28/2018																$2,656.33	6001
208			Coupon 1				1		$0.00			$0.00			$2.59	100.00%		0.00%	6001
209	5/28/2018 Total:						1					$0.00			$2.59			0.00%	6001
210		5/29/2018																$3,624.37	6001
211			Coupon 1				1		$0.00			$0.00			$2.59	100.00%		0.00%	6001
212	5/29/2018 Total:						1					$0.00			$2.59			0.00%	6001
213		5/30/2018																$3,640.76	6001
214							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
215	5/30/2018 Total:						0					$0.00			$0.00			0.00%	6001
216		5/31/2018																$3,743.24	6001
217			Coupon 1				3		$0.00			$0.00			$7.77	100.00%		0.00%	6001
218	5/31/2018 Total:						3					$0.00			$7.77			0.00%	6001
219		6/1/2018																$4,338.43	6001
220			Coupon 1				1		$3.19			$3.19			$3.20	100.00%		0.07%	6001
221	6/1/2018 Total:						1					$3.19			$3.20			0.07%	6001
222		6/2/2018																$3,762.59	6001
223							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
224	6/2/2018 Total:						0					$0.00			$0.00			0.00%	6001
225		6/3/2018																$3,519.21	6001
226			Coupon 1				1		$0.00			$0.00			$4.09	100.00%		0.00%	6001
227	6/3/2018 Total:						1					$0.00			$4.09			0.00%	6001
228		6/4/2018																$3,593.66	6001
229			Coupon 1				1		$0.00			$0.00			$4.69	100.00%		0.00%	6001
230	6/4/2018 Total:						1					$0.00			$4.69			0.00%	6001
231		6/5/2018																$3,320.13	6001
232			Coupon 1				1		$0.00			$0.00			$3.49	100.00%		0.00%	6001
233	6/5/2018 Total:						1					$0.00			$3.49			0.00%	6001
234		6/6/2018																$3,787.81	6001
235			Coupon 1				1		$4.79			$4.79			$2.00	100.00%		0.13%	6001
236	6/6/2018 Total:						1					$4.79			$2.00			0.13%	6001
237		6/7/2018																$3,426.56	6001
238																			6001
239																			6001
240																			6001
241							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
242	6/7/2018 Total:						0					$0.00			$0.00			0.00%	6001
243		6/8/2018																$4,121.41	6001
244							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
245	6/8/2018 Total:						0					$0.00			$0.00			0.00%	6001
246		6/9/2018																$4,075.87	6001
247							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
248	6/9/2018 Total:						0					$0.00			$0.00			0.00%	6001
249		6/10/2018																$3,145.40	6001
250							0		$0.00			$0.00			$0.00	0.00%		0.00%	6001
251	6/10/2018 Total:						0					$0.00			$0.00			0.00%	6001
252		6/11/2018																$3,370.53	43626
253			Coupon 1				1		$0.00			$0.00			$3.49	100.00%		0.00%	43626
254	6/11/2018 Total:						1					$0.00			$3.49			0.00%	43626
255		6/12/2018																$3,212.89	43627
256			Coupon 1				1		$0.00			$0.00			$0.99	100.00%		0.00%	43627
257	6/12/2018 Total:						1					$0.00			$0.99			0.00%	43627
258		6/13/2018																$3,328.96	43628
259			Coupon 1				1		($0.34)			($0.34)			$2.19	68.87%		-0.01%	43628
260			Coupon 1				1		$0.00			$0.00			$0.99	31.13%		0.00%	43628
261	6/13/2018 Total:						2					($0.34)			$3.18			-0.01%	43628
262		6/14/2018																$3,928.13	43629
263			Coupon 1				1		$0.00			$0.00			$4.69	100.00%		0.00%	43629
264	6/14/2018 Total:						1					$0.00			$4.69			0.00%	43629
265		6/15/2018																$4,605.78	43630
266							0		$0.00			$0.00			$0.00	0.00%		0.00%	43630
267	6/15/2018 Total:						0					$0.00			$0.00			0.00%	43630
268		6/16/2018																$3,306.14	43631
269							0		$0.00			$0.00			$0.00	0.00%		0.00%	43631
270	6/16/2018 Total:						0					$0.00			$0.00			0.00%	43631
271		6/17/2018																$2,586.95	43632
272			Coupon 1				2		($0.05)			($0.10)			$4.38	47.25%		0.00%	43632
273			Coupon 1				1		$0.00			$0.00			$4.89	52.75%		0.00%	43632
274	6/17/2018 Total:						3					($0.10)			$9.27			0.00%	43632
275		6/18/2018																$3,709.32	43633
276							0		$0.00			$0.00			$0.00	0.00%		0.00%	43633
277	6/18/2018 Total:						0					$0.00			$0.00			0.00%	43633
278		6/19/2018																$3,197.75	43634
279							0		$0.00			$0.00			$0.00	0.00%		0.00%	43634
280	6/19/2018 Total:						0					$0.00			$0.00			0.00%	43634
281		6/20/2018																$3,416.92	43635
282							0		$0.00			$0.00			$0.00	0.00%		0.00%	43635
283	6/20/2018 Total:						0					$0.00			$0.00			0.00%	43635
284		6/21/2018																$3,843.91	43636

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1 (2)​

Worksheet Formulas Cell Formula S194 =IF(AND(ISNUMBER(VALUE(LEFT(A193,4))),LEFT(A193,1)="6"),VALUE(LEFT(A193,4)),S193) S195 =IF(AND(ISNUMBER(VALUE(LEFT(A194,4))),LEFT(A194,1)="6"),VALUE(LEFT(A194,4)),S194) S196 =IF(AND(ISNUMBER(VALUE(LEFT(A195,4))),LEFT(A195,1)="6"),VALUE(LEFT(A195,4)),S195) S197 =IF(AND(ISNUMBER(VALUE(LEFT(A196,4))),LEFT(A196,1)="6"),VALUE(LEFT(A196,4)),S196) S198 =IF(AND(ISNUMBER(VALUE(LEFT(A197,4))),LEFT(A197,1)="6"),VALUE(LEFT(A197,4)),S197) S199 =IF(AND(ISNUMBER(VALUE(LEFT(A198,4))),LEFT(A198,1)="6"),VALUE(LEFT(A198,4)),S198) S200 =IF(AND(ISNUMBER(VALUE(LEFT(A199,4))),LEFT(A199,1)="6"),VALUE(LEFT(A199,4)),S199) S201 =IF(AND(ISNUMBER(VALUE(LEFT(A200,4))),LEFT(A200,1)="6"),VALUE(LEFT(A200,4)),S200) S202 =IF(AND(ISNUMBER(VALUE(LEFT(A201,4))),LEFT(A201,1)="6"),VALUE(LEFT(A201,4)),S201) S203 =IF(AND(ISNUMBER(VALUE(LEFT(A202,4))),LEFT(A202,1)="6"),VALUE(LEFT(A202,4)),S202) S204 =IF(AND(ISNUMBER(VALUE(LEFT(A203,4))),LEFT(A203,1)="6"),VALUE(LEFT(A203,4)),S203) S205 =IF(AND(ISNUMBER(VALUE(LEFT(A204,4))),LEFT(A204,1)="6"),VALUE(LEFT(A204,4)),S204) S206 =IF(AND(ISNUMBER(VALUE(LEFT(A205,4))),LEFT(A205,1)="6"),VALUE(LEFT(A205,4)),S205) S207 =IF(AND(ISNUMBER(VALUE(LEFT(A206,4))),LEFT(A206,1)="6"),VALUE(LEFT(A206,4)),S206) S208 =IF(AND(ISNUMBER(VALUE(LEFT(A207,4))),LEFT(A207,1)="6"),VALUE(LEFT(A207,4)),S207) S209 =IF(AND(ISNUMBER(VALUE(LEFT(A208,4))),LEFT(A208,1)="6"),VALUE(LEFT(A208,4)),S208) S210 =IF(AND(ISNUMBER(VALUE(LEFT(A209,4))),LEFT(A209,1)="6"),VALUE(LEFT(A209,4)),S209) S211 =IF(AND(ISNUMBER(VALUE(LEFT(A210,4))),LEFT(A210,1)="6"),VALUE(LEFT(A210,4)),S210) S212 =IF(AND(ISNUMBER(VALUE(LEFT(A211,4))),LEFT(A211,1)="6"),VALUE(LEFT(A211,4)),S211) S213 =IF(AND(ISNUMBER(VALUE(LEFT(A212,4))),LEFT(A212,1)="6"),VALUE(LEFT(A212,4)),S212) S214 =IF(AND(ISNUMBER(VALUE(LEFT(A213,4))),LEFT(A213,1)="6"),VALUE(LEFT(A213,4)),S213) S215 =IF(AND(ISNUMBER(VALUE(LEFT(A214,4))),LEFT(A214,1)="6"),VALUE(LEFT(A214,4)),S214) S216 =IF(AND(ISNUMBER(VALUE(LEFT(A215,4))),LEFT(A215,1)="6"),VALUE(LEFT(A215,4)),S215) S217 =IF(AND(ISNUMBER(VALUE(LEFT(A216,4))),LEFT(A216,1)="6"),VALUE(LEFT(A216,4)),S216) S218 =IF(AND(ISNUMBER(VALUE(LEFT(A217,4))),LEFT(A217,1)="6"),VALUE(LEFT(A217,4)),S217) S219 =IF(AND(ISNUMBER(VALUE(LEFT(A218,4))),LEFT(A218,1)="6"),VALUE(LEFT(A218,4)),S218) S220 =IF(AND(ISNUMBER(VALUE(LEFT(A219,4))),LEFT(A219,1)="6"),VALUE(LEFT(A219,4)),S219) S221 =IF(AND(ISNUMBER(VALUE(LEFT(A220,4))),LEFT(A220,1)="6"),VALUE(LEFT(A220,4)),S220) S222 =IF(AND(ISNUMBER(VALUE(LEFT(A221,4))),LEFT(A221,1)="6"),VALUE(LEFT(A221,4)),S221) S223 =IF(AND(ISNUMBER(VALUE(LEFT(A222,4))),LEFT(A222,1)="6"),VALUE(LEFT(A222,4)),S222) S224 =IF(AND(ISNUMBER(VALUE(LEFT(A223,4))),LEFT(A223,1)="6"),VALUE(LEFT(A223,4)),S223) S225 =IF(AND(ISNUMBER(VALUE(LEFT(A224,4))),LEFT(A224,1)="6"),VALUE(LEFT(A224,4)),S224) S226 =IF(AND(ISNUMBER(VALUE(LEFT(A225,4))),LEFT(A225,1)="6"),VALUE(LEFT(A225,4)),S225) S227 =IF(AND(ISNUMBER(VALUE(LEFT(A226,4))),LEFT(A226,1)="6"),VALUE(LEFT(A226,4)),S226) S228 =IF(AND(ISNUMBER(VALUE(LEFT(A227,4))),LEFT(A227,1)="6"),VALUE(LEFT(A227,4)),S227) S229 =IF(AND(ISNUMBER(VALUE(LEFT(A228,4))),LEFT(A228,1)="6"),VALUE(LEFT(A228,4)),S228) S230 =IF(AND(ISNUMBER(VALUE(LEFT(A229,4))),LEFT(A229,1)="6"),VALUE(LEFT(A229,4)),S229) S231 =IF(AND(ISNUMBER(VALUE(LEFT(A230,4))),LEFT(A230,1)="6"),VALUE(LEFT(A230,4)),S230) S232 =IF(AND(ISNUMBER(VALUE(LEFT(A231,4))),LEFT(A231,1)="6"),VALUE(LEFT(A231,4)),S231) S233 =IF(AND(ISNUMBER(VALUE(LEFT(A232,4))),LEFT(A232,1)="6"),VALUE(LEFT(A232,4)),S232) S234 =IF(AND(ISNUMBER(VALUE(LEFT(A233,4))),LEFT(A233,1)="6"),VALUE(LEFT(A233,4)),S233) S235 =IF(AND(ISNUMBER(VALUE(LEFT(A234,4))),LEFT(A234,1)="6"),VALUE(LEFT(A234,4)),S234) S236 =IF(AND(ISNUMBER(VALUE(LEFT(A235,4))),LEFT(A235,1)="6"),VALUE(LEFT(A235,4)),S235) S237 =IF(AND(ISNUMBER(VALUE(LEFT(A236,4))),LEFT(A236,1)="6"),VALUE(LEFT(A236,4)),S236) S238 =IF(AND(ISNUMBER(VALUE(LEFT(A237,4))),LEFT(A237,1)="6"),VALUE(LEFT(A237,4)),S237) S239 =IF(AND(ISNUMBER(VALUE(LEFT(A238,4))),LEFT(A238,1)="6"),VALUE(LEFT(A238,4)),S238) S240 =IF(AND(ISNUMBER(VALUE(LEFT(A239,4))),LEFT(A239,1)="6"),VALUE(LEFT(A239,4)),S239) S241 =IF(AND(ISNUMBER(VALUE(LEFT(A240,4))),LEFT(A240,1)="6"),VALUE(LEFT(A240,4)),S240) S242 =IF(AND(ISNUMBER(VALUE(LEFT(A241,4))),LEFT(A241,1)="6"),VALUE(LEFT(A241,4)),S241) S243 =IF(AND(ISNUMBER(VALUE(LEFT(A242,4))),LEFT(A242,1)="6"),VALUE(LEFT(A242,4)),S242) S244 =IF(AND(ISNUMBER(VALUE(LEFT(A243,4))),LEFT(A243,1)="6"),VALUE(LEFT(A243,4)),S243) S245 =IF(AND(ISNUMBER(VALUE(LEFT(A244,4))),LEFT(A244,1)="6"),VALUE(LEFT(A244,4)),S244) S246 =IF(AND(ISNUMBER(VALUE(LEFT(A245,4))),LEFT(A245,1)="6"),VALUE(LEFT(A245,4)),S245) S247 =IF(AND(ISNUMBER(VALUE(LEFT(A246,4))),LEFT(A246,1)="6"),VALUE(LEFT(A246,4)),S246) S248 =IF(AND(ISNUMBER(VALUE(LEFT(A247,4))),LEFT(A247,1)="6"),VALUE(LEFT(A247,4)),S247) S249 =IF(AND(ISNUMBER(VALUE(LEFT(A248,4))),LEFT(A248,1)="6"),VALUE(LEFT(A248,4)),S248) S250 =IF(AND(ISNUMBER(VALUE(LEFT(A249,4))),LEFT(A249,1)="6"),VALUE(LEFT(A249,4)),S249) S251 =IF(AND(ISNUMBER(VALUE(LEFT(A250,4))),LEFT(A250,1)="6"),VALUE(LEFT(A250,4)),S250) S252 =IF(AND(ISNUMBER(VALUE(LEFT(A251,4))),LEFT(A251,1)="6"),VALUE(LEFT(A251,4)),S251) S253 =IF(AND(ISNUMBER(VALUE(LEFT(A252,4))),LEFT(A252,1)="6"),VALUE(LEFT(A252,4)),S252) S254 =IF(AND(ISNUMBER(VALUE(LEFT(A253,4))),LEFT(A253,1)="6"),VALUE(LEFT(A253,4)),S253) S255 =IF(AND(ISNUMBER(VALUE(LEFT(A254,4))),LEFT(A254,1)="6"),VALUE(LEFT(A254,4)),S254) S256 =IF(AND(ISNUMBER(VALUE(LEFT(A255,4))),LEFT(A255,1)="6"),VALUE(LEFT(A255,4)),S255) S257 =IF(AND(ISNUMBER(VALUE(LEFT(A256,4))),LEFT(A256,1)="6"),VALUE(LEFT(A256,4)),S256) S258 =IF(AND(ISNUMBER(VALUE(LEFT(A257,4))),LEFT(A257,1)="6"),VALUE(LEFT(A257,4)),S257) S259 =IF(AND(ISNUMBER(VALUE(LEFT(A258,4))),LEFT(A258,1)="6"),VALUE(LEFT(A258,4)),S258) S260 =IF(AND(ISNUMBER(VALUE(LEFT(A259,4))),LEFT(A259,1)="6"),VALUE(LEFT(A259,4)),S259) S261 =IF(AND(ISNUMBER(VALUE(LEFT(A260,4))),LEFT(A260,1)="6"),VALUE(LEFT(A260,4)),S260) S262 =IF(AND(ISNUMBER(VALUE(LEFT(A261,4))),LEFT(A261,1)="6"),VALUE(LEFT(A261,4)),S261) S263 =IF(AND(ISNUMBER(VALUE(LEFT(A262,4))),LEFT(A262,1)="6"),VALUE(LEFT(A262,4)),S262) S264 =IF(AND(ISNUMBER(VALUE(LEFT(A263,4))),LEFT(A263,1)="6"),VALUE(LEFT(A263,4)),S263) S265 =IF(AND(ISNUMBER(VALUE(LEFT(A264,4))),LEFT(A264,1)="6"),VALUE(LEFT(A264,4)),S264) S266 =IF(AND(ISNUMBER(VALUE(LEFT(A265,4))),LEFT(A265,1)="6"),VALUE(LEFT(A265,4)),S265) S267 =IF(AND(ISNUMBER(VALUE(LEFT(A266,4))),LEFT(A266,1)="6"),VALUE(LEFT(A266,4)),S266) S268 =IF(AND(ISNUMBER(VALUE(LEFT(A267,4))),LEFT(A267,1)="6"),VALUE(LEFT(A267,4)),S267) S269 =IF(AND(ISNUMBER(VALUE(LEFT(A268,4))),LEFT(A268,1)="6"),VALUE(LEFT(A268,4)),S268) S270 =IF(AND(ISNUMBER(VALUE(LEFT(A269,4))),LEFT(A269,1)="6"),VALUE(LEFT(A269,4)),S269) S271 =IF(AND(ISNUMBER(VALUE(LEFT(A270,4))),LEFT(A270,1)="6"),VALUE(LEFT(A270,4)),S270) S272 =IF(AND(ISNUMBER(VALUE(LEFT(A271,4))),LEFT(A271,1)="6"),VALUE(LEFT(A271,4)),S271) S273 =IF(AND(ISNUMBER(VALUE(LEFT(A272,4))),LEFT(A272,1)="6"),VALUE(LEFT(A272,4)),S272) S274 =IF(AND(ISNUMBER(VALUE(LEFT(A273,4))),LEFT(A273,1)="6"),VALUE(LEFT(A273,4)),S273) S275 =IF(AND(ISNUMBER(VALUE(LEFT(A274,4))),LEFT(A274,1)="6"),VALUE(LEFT(A274,4)),S274) S276 =IF(AND(ISNUMBER(VALUE(LEFT(A275,4))),LEFT(A275,1)="6"),VALUE(LEFT(A275,4)),S275) S277 =IF(AND(ISNUMBER(VALUE(LEFT(A276,4))),LEFT(A276,1)="6"),VALUE(LEFT(A276,4)),S276) S278 =IF(AND(ISNUMBER(VALUE(LEFT(A277,4))),LEFT(A277,1)="6"),VALUE(LEFT(A277,4)),S277) S279 =IF(AND(ISNUMBER(VALUE(LEFT(A278,4))),LEFT(A278,1)="6"),VALUE(LEFT(A278,4)),S278) S280 =IF(AND(ISNUMBER(VALUE(LEFT(A279,4))),LEFT(A279,1)="6"),VALUE(LEFT(A279,4)),S279) S281 =IF(AND(ISNUMBER(VALUE(LEFT(A280,4))),LEFT(A280,1)="6"),VALUE(LEFT(A280,4)),S280) S282 =IF(AND(ISNUMBER(VALUE(LEFT(A281,4))),LEFT(A281,1)="6"),VALUE(LEFT(A281,4)),S281) S283 =IF(AND(ISNUMBER(VALUE(LEFT(A282,4))),LEFT(A282,1)="6"),VALUE(LEFT(A282,4)),S282) S284 =IF(AND(ISNUMBER(VALUE(LEFT(A283,4))),LEFT(A283,1)="6"),VALUE(LEFT(A283,4)),S283) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[t0azt]

Re: If cell's contains this, do this until...

Add an additional constraint that the value has to be less than 7,000? Excel sees dates as numbers so 1=1/1/1900 and june 2018 is that 43,000ish you're getting.