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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]Item 1 [/TD]
[TD]Item 2[/TD]
[TD]Item 3[/TD]
[TD]Item 4[/TD]
[TD]Item 5[/TD]
[TD]Item 6[/TD]
[/TR]
[TR]
[TD]Fred [/TD]
[TD]Nerd[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Nerd[/TD]
[TD]4321[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Surname[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]Items[/TD]
[TD]1[/TD]
[TD] 2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Nerd[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD]1,3-5[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Sally [/TD]
[TD]Nerd[/TD]
[TD]4321[/TD]
[TD][/TD]
[TD]2,3,5[/TD]
[TD][/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[TD]

Q​

[/TD]
[TD]

R​

[/TD]
[TD]

S​

[/TD]
[TD]

T​

[/TD]
[TD]

U​

[/TD]
[TD]

V​

[/TD]
[TD]

W​

[/TD]
[TD]

X​

[/TD]
[TD]

Y​

[/TD]
[TD]

Z​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

First Name​

[/TD]
[TD]

Surname​

[/TD]
[TD]

ID​

[/TD]
[TD][/TD]
[TD]

Items​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD]

10​

[/TD]
[TD]

11​

[/TD]
[TD]

12​

[/TD]
[TD]

13​

[/TD]
[TD]

14​

[/TD]
[TD]

15​

[/TD]
[TD]

16​

[/TD]
[TD]

17​

[/TD]
[TD]

18​

[/TD]
[TD]

19​

[/TD]
[TD]

20​

[/TD]
[TD]

Helper​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Fred​

[/TD]
[TD]

Nerd​

[/TD]
[TD]

1234​

[/TD]
[TD][/TD]
[TD]

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

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD]

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

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Sally​

[/TD]
[TD]

Nerd​

[/TD]
[TD]

4321​

[/TD]
[TD][/TD]
[TD]

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

[/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD]

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

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

John​

[/TD]
[TD]

Nerd​

[/TD]
[TD]

5678​

[/TD]
[TD][/TD]
[TD]

1,7,14,18​

[/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Y​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

,1,7,14,18,​

[/TD]
[/TR]
</TBODY>[/TABLE]


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.