Look For 12 Digit Number & Do Some Calculations

[dgr]

Hello,
I'm using Excel 2013. I'm looking for a vb macro solution.

I want to find all 12 digit numbers in column C. If this 12 digit number is an odd number, then I would like to enter the number "2" in column E on the same row as the 12 digit digit number. If this 12 digit number is an even number, then I would like to enter the number "1" in column E on the same row as the 12 digit digit number.

For example:

<b>Column C</b> ........................... <b>Column E</b>
<font color="blue">860512125211 ..................... 2
86051212 ............................ ignore as this is not a 12 digit number
850524085748 ..................... 1
860407435242 ..................... 1
hello ................................. ignore as this is not a 12 digit number</font>

Could you show me the code to achieve this please?

Thanks a lot.

 

[Jonmo1]

Try

=IF(LEN(C2)=12,ISODD(C2)+1,"")

 

[VBA Geek]

Sub Look_For_12_Digits()
Dim A
With Range("c1:c" & Range("c" & Rows.Count).End(xlUp).Row)
    A = Evaluate(Replace$("=IF(ISNUMBER(@)*(LEN(@)=12),IF(MOD(@,2),2,1),"""")", "@", .Address))
    .Offset(, 2).Value = A
End With
End Sub

 

[dgr]

Thanks for your reply VBA Geek. All I got was #NUM! in column E. Why is that so?

 

[dgr]

Thanks for your reply, MrExcel MVP. Sorry, I'm looking for a vb macro solution. Nevertheless, I tried your formula. The cell in which I pasted the formula is blank.

 

[jolivanes]

You can use Jonmo's formula in code.

Sub Odd_Or_Even()
    With Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row).Offset(, 2)
        .Formula = "=IF(LEN(RC[-2])=12,ISODD(RC[-2])+1,"""")"
        .Value = .Value
    End With
End Sub

 

[dgr]

You can use Jonmo's formula in code.

Sub Odd_Or_Even()
    With Range("C2:C" & Cells(Rows.Count, 3).End(xlUp).Row).Offset(, 2)
        .Formula = "=IF(LEN(RC[-2])=12,ISODD(RC[-2])+1,"""")"
        .Value = .Value
    End With
End Sub

Thanks a lot jolivanes. This worked well. I appreciate your help. Have a blessed day

 

[jolivanes]

Glad it worked for you.
Thanks for letting us know.