Returning a value if a cell contains number text with hyphens or other

[cooke1]

Hi All,

Thanks in advance for your help. Currently I have been provided with an excel template that requires me to select specific items to be assigned to a user; this data will then be imported into an SQL database.

Below is the template format I am locked into.

First Name	Surname	ID		Item 1	Item 2	Item 3	Item 4	Item 5	Item 6
Fred	Nerd	1234		Y		Y	Y	Y
Sally	Nerd	4321			Y	Y			Y

<tbody>
</tbody>

Now what I would like to do instead of going across and selecting Y from the drop down is to write a formula that allows for an auto insert of Y based on text in a field as per the below.

First Name	Surname	ID		Items	1	2	3	4	5
Fred	Nerd	1234		1,3-5	Y		Y	Y	Y
Sally	Nerd	4321		2,3,5		Y	Y		Y

<tbody>
</tbody>

If I am happy to individually comma separate the numbers then it is easy as I can just do =IF(ISNUMBER(SEARCH(E1,$F$2)),"Y","N")

The problem I have is that I have literally hundreds of items and would love to just be able to enter 1-5 and have it return a Y for any number between 1 and 5. Of course I am happy for it not to be a hyphen.

Looking forward to someone smarter than me lending a helping hand.

 

[bschwartz]

Hi,

Welcome to the Mr Excel boards.

Maybe something like this?

Excel 2010
	A	B	C	D	E	F	G	H	I
1	First Name	Surname	ID	Items	1	2	3	4	5
2	Fred	Nerd	1234	1,3,4,5	Y		Y	Y	Y
3	Sally	Nerd	4321	2,3,5		Y	Y		Y
Item

Cell Formulas
Range	Formula
E2	=IFERROR(IF(SEARCH(E$1,$D2),"Y"),"")
F2	=IFERROR(IF(SEARCH(F$1,$D2),"Y"),"")
G2	=IFERROR(IF(SEARCH(G$1,$D2),"Y"),"")
H2	=IFERROR(IF(SEARCH(H$1,$D2),"Y"),"")
I2	=IFERROR(IF(SEARCH(I$1,$D2),"Y"),"")

 

[bschwartz]

Oh, I see, you do NOT want to use commas...

 

[Marcelo Branco]

Two possible solutions

1st (with a helper column)

Paste the UDF below on a Standard Module
Alt+F11 to open VBA Editor
Insert > Module
Paste the code below in the right panel

Function CreateString(s As String) As String
    Dim spl1 As Variant, spl2 As Variant
    Dim i As Long, j As Long
        
    spl1 = Split(s, ",")
    For i = 0 To UBound(spl1)
        If InStr(1, spl1(i), "-") = 0 Then
            CreateString = CreateString & Trim(spl1(i)) & ","
        Else
            spl2 = Split(spl1(i), "-")
            For j = spl2(0) To spl2(1)
                CreateString = CreateString & j & ","
            Next j
        End If
    Next i
    
    CreateString = "," & CreateString
End Function

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

Q​

R​

S​

T​

U​

V​

W​

X​

Y​

Z​

1​

First Name​

Surname​

ID​

Items​

1​

2​

3​

4​

5​

6​

7​

8​

9​

10​

11​

12​

13​

14​

15​

16​

17​

18​

19​

20​

Helper​

2​

Fred​

Nerd​

1234​

1,3-7,10-14,17,20​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

,1,3,4,5,6,7,10,11,12,13,14,17,20,​

3​

Sally​

Nerd​

4321​

2,3,5-11,17-19​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

Y​

,2,3,5,6,7,8,9,10,11,17,18,19,​

4​

John​

Nerd​

5678​

1,7,14,18​

Y​

Y​

Y​

Y​

,1,7,14,18,​

<TBODY>
</TBODY>

Helper column
Z2 formula
=CreateString(E2)
copy down


F2 Formula
=IF(ISNUMBER(SEARCH(","&F$1&",",$Z2)),"Y","")
copy across till Y2 and down

2nd (no helper column)

Use this UDF

Function CreateArray(s As String) As Variant
    Dim spl1 As Variant, spl2 As Variant, dict As Object
    Dim i As Long, j As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    spl1 = Split(s, ",")
    For i = 0 To UBound(spl1)
        If InStr(1, spl1(i), "-") = 0 Then
            dict(CLng(spl1(i))) = Empty
        Else
            spl2 = Split(spl1(i), "-")
            For j = spl2(0) To spl2(1)
                dict(j) = Empty
            Next j
        End If
    Next i
    
    CreateArray = dict.keys
    
End Function

F2 formula
=IF(ISNUMBER(MATCH(F$1,CreateArray($E2),0)),"Y","")
copy across till Y2 and down

Hope this helps

M.