Need Formula

CARBOB · Apr 4, 2011

Columns O-S contain values that range from 1-36. Need a formula in Col T that will look for digits 1-4 and return a 1, if found, a zero if not. The same for digits 2,3,4. Example in column T. Then in column S, a formula to look for the digits 5,6,7,8,9,0. Return a 1, if found, a zero if not. Example in column U.

Excel Workbook

O

P

Q

R

S

T

U

1

19

24

28

31

36

1111

010110

2

9

15

17

20

21

1100

101011

3

7

8

14

22

27

1101

001100

4

10

18

28

29

34

1111

000111

5

22

23

24

35

36

0111

111000

Sheet1

Excel 2007

MrKowz · Apr 4, 2011

Try this UDF:

Code:

Public Function FindNumeric(rng As Range, NumStr As Long) As String
Dim rrng    As Range, _
    i       As Long, _
    bool    As Boolean
    
For Each rrng In rng
    bool = False
    For i = 1 To Len(rrng.Value)
        If Mid(rrng.Value, i, 1) Like "[" & NumStr & "]" Then
            FindNumeric = FindNumeric & 1
            bool = True
            Exit For
        End If
    Next i
    If bool = False Then FindNumeric = FindNumeric & 0
Next rrng
End Function

With usage as follows:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">19</td><td style="text-align: center;color: #333333;;">24</td><td style="text-align: center;color: #333333;;">28</td><td style="text-align: center;color: #333333;;">31</td><td style="text-align: center;color: #333333;;">36</td><td style="text-align: center;color: #333333;;">11111</td><td style="text-align: center;color: #333333;;">01111</td><td style="text-align: center;color: #333333;;">10101</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">9</td><td style="text-align: center;color: #333333;;">15</td><td style="text-align: center;color: #333333;;">17</td><td style="text-align: center;color: #333333;;">20</td><td style="text-align: center;color: #333333;;">21</td><td style="text-align: center;color: #333333;;">01111</td><td style="text-align: center;color: #333333;;">00011</td><td style="text-align: center;color: #333333;;">11110</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">7</td><td style="text-align: center;color: #333333;;">8</td><td style="text-align: center;color: #333333;;">14</td><td style="text-align: center;color: #333333;;">22</td><td style="text-align: center;color: #333333;;">27</td><td style="text-align: center;color: #333333;;">00111</td><td style="text-align: center;color: #333333;;">00111</td><td style="text-align: center;color: #333333;;">11001</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">10</td><td style="text-align: center;color: #333333;;">18</td><td style="text-align: center;color: #333333;;">28</td><td style="text-align: center;color: #333333;;">29</td><td style="text-align: center;color: #333333;;">34</td><td style="text-align: center;color: #333333;;">11111</td><td style="text-align: center;color: #333333;;">00111</td><td style="text-align: center;color: #333333;;">11110</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">22</td><td style="text-align: center;color: #333333;;">23</td><td style="text-align: center;color: #333333;;">24</td><td style="text-align: center;color: #333333;;">35</td><td style="text-align: center;color: #333333;;">36</td><td style="text-align: center;color: #333333;;">11111</td><td style="text-align: center;color: #333333;;">11111</td><td style="text-align: center;color: #333333;;">00011</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T1</th><td style="text-align:left">=findnumeric(<font color="Blue">O1:S1,1234</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U1</th><td style="text-align:left">=findnumeric(<font color="Blue">O1:S1,234</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">V1</th><td style="text-align:left">=findnumeric(<font color="Blue">O1:S1,567890</font>)</td></tr></tbody></table></td></tr></table><br />

CARBOB · Apr 4, 2011

Thank you for responding. Should only return 4 values for Col T and 6 values for Col u. Have never used UDF's.

MrKowz · Apr 4, 2011

Ahh - it took me a moment to understand your logic, but after staring at it for a minute, I think I see it:

Code:

Public Function FindNumeric(rng As Range, NumStr As String) As String
Dim rrng    As Range, _
    i       As Long, _
    bool    As Boolean
For i = 1 To Len(NumStr)
    For Each rrng In rng
        bool = False
        If InStr(rrng.Value, Mid(NumStr, i, 1)) > 0 Then
            FindNumeric = FindNumeric & 1
            bool = True
            Exit For
        End If
    Next rrng
    If bool = False Then FindNumeric = FindNumeric & 0
Next i
End Function

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;color: #333333;;">19</td><td style="text-align: center;color: #333333;;">24</td><td style="text-align: center;color: #333333;;">28</td><td style="text-align: center;color: #333333;;">31</td><td style="text-align: center;color: #333333;;">36</td><td style="text-align: center;color: #333333;;">1111</td><td style="text-align: center;color: #333333;;">111</td><td style="text-align: center;color: #333333;;">010110</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">9</td><td style="text-align: center;color: #333333;;">15</td><td style="text-align: center;color: #333333;;">17</td><td style="text-align: center;color: #333333;;">20</td><td style="text-align: center;color: #333333;;">21</td><td style="text-align: center;color: #333333;;">1100</td><td style="text-align: center;color: #333333;;">100</td><td style="text-align: center;color: #333333;;">101011</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">7</td><td style="text-align: center;color: #333333;;">8</td><td style="text-align: center;color: #333333;;">14</td><td style="text-align: center;color: #333333;;">22</td><td style="text-align: center;color: #333333;;">27</td><td style="text-align: center;color: #333333;;">1101</td><td style="text-align: center;color: #333333;;">101</td><td style="text-align: center;color: #333333;;">001100</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">10</td><td style="text-align: center;color: #333333;;">18</td><td style="text-align: center;color: #333333;;">28</td><td style="text-align: center;color: #333333;;">29</td><td style="text-align: center;color: #333333;;">34</td><td style="text-align: center;color: #333333;;">1111</td><td style="text-align: center;color: #333333;;">111</td><td style="text-align: center;color: #333333;;">000111</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">22</td><td style="text-align: center;color: #333333;;">23</td><td style="text-align: center;color: #333333;;">24</td><td style="text-align: center;color: #333333;;">35</td><td style="text-align: center;color: #333333;;">36</td><td style="text-align: center;color: #333333;;">0111</td><td style="text-align: center;color: #333333;;">111</td><td style="text-align: center;color: #333333;;">110000</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">T1</th><td style="text-align:left">=findnumeric(<font color="Blue">O1:S1,1234</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U1</th><td style="text-align:left">=findnumeric(<font color="Blue">O1:S1,234</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">V1</th><td style="text-align:left">=findnumeric(<font color="Blue">O1:S1,567890</font>)</td></tr></tbody></table></td></tr></table><br />

To use the UDF, while in your worksheet, do the following:

Press Alt+F11 to bring up the VBA Editor
Insert>Module
Copy/paste the VBA code into the module
In your worksheet, follow the example above

CARBOB · Apr 4, 2011

Thank you, that works!

Need Formula

CARBOB

Well-known Member

Excel Facts

MrKowz

Well-known Member

CARBOB

Well-known Member

MrKowz

Well-known Member

CARBOB

Well-known Member

Similar threads

Forum statistics

Share this page

Need Formula

CARBOB

Well-known Member

Excel Facts

MrKowz

Well-known Member

CARBOB

Well-known Member

MrKowz

Well-known Member

CARBOB

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock