SOLVED

IF(LEFT(ISNUMBER statement

%3CLINGO-SUB%20id%3D%22lingo-sub-3092638%22%20slang%3D%22en-US%22%3EIF(LEFT(ISNUMBER%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092638%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20use%20an%20IF%20statement%20to%20pull%20the%20left%206%20characters%2C%20ONLY%20if%20they%20are%20numbers.%20Example%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E184412%20MAIN%20FRAME%20UPDATE%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3092638%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092764%22%20slang%3D%22en-US%22%3ERe%3A%20IF(LEFT(ISNUMBER%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200123%22%20target%3D%22_blank%22%3E%40bfield66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(%20ISERROR(%20LEFT(A13%2C6)*1%20)%2C%20%22%22%2C%20LEFT(A13%2C6)*1%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092881%22%20slang%3D%22en-US%22%3ERe%3A%20IF(LEFT(ISNUMBER%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092881%22%20slang%3D%22en-US%22%3ESo%20close!%20I%20returns%20just%20the%20first%20digit.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092891%22%20slang%3D%22en-US%22%3ERe%3A%20IF(LEFT(ISNUMBER%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092891%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1200123%22%20target%3D%22_blank%22%3E%40bfield66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(SUM((LEN(LEFT(C4%2C6))-LEN(SUBSTITUTE(LEFT(C4%2C6)%2C%24A%244%3A%24A%2413%2C)))%2FLEN(%24A%244%3A%24A%2413))%3D6%2CLEFT(C4%2C6)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20alternative%20could%20be%20above%20formula%20as%20shown%20in%20the%20attached%20file.%20Enter%20formula%20with%20ctrl%2Bshift%2Benter%20if%20you%20don't%20work%20with%20Office365%20or%202021.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I'm trying to use an IF statement to pull the left 6 characters, ONLY if they are numbers. Example below.

 

184412 MAIN FRAME UPDATE

4 Replies
best response confirmed by bfield66 (Occasional Contributor)
Solution

@bfield66 

That could be

 

=IF( ISERROR( LEFT(A1,6)*1 ), "", LEFT(A1,6)*1 )

 

So close! I returns just the first digit.

@bfield66 

=IF(SUM((LEN(LEFT(C4,6))-LEN(SUBSTITUTE(LEFT(C4,6),$A$4:$A$13,)))/LEN($A$4:$A$13))=6,LEFT(C4,6),"")

 

An alternative could be above formula as shown in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.

Sorry, my mistake!! You are absolutely correct!!