Splitting a list of values in separate columns

I am trying to put something together on Excel and I wanted to get input from the community.

1. In my first column(Let's call it "input"), there will be a variety of values from 1-10 which will appear in cells as 1,7,10 or 4,5,6,7 or any other similar variation. The quantity of numbers can vary and will always be comma delimited.
2. Following the first column are 10 additional columns. For simplicity, let's say they're labelled 1-10.
3. If 2,5,6,9 is present in the first cell, I want the number 1 in the columns labelled 2,5,6 and 9.
If 1,4 is present in the first cell, I want the number 1 in the columns labelled 1 and 4.

The input will only be in the first cell so I am hoping to find a way to automate the placement of 1's in the appropriate column depending on the input in the first column("input").

As a bonus, I'd like all cells not present in the first column to appear as a "0".

Any suggestions, ideas or nudges in the right direction will be appreciated.

Reapz,

I was playing with this too. There's a problem with your solution (that I haven't got round yet, either). If there's no number 1, but there is a 10 in the string, your formula thinks that there's a 1 there.

This is a bit of a cheat, but it works. Anyone have a better idea?? (The cheat is the comma after the 1 in cell B1)

Need more info. Better if you upload a sample workbook with expected output filled manually and explaining that why do you expect that output in the sheet itself. That will help us to know your requirement.

Hello Sioux ! Assuming your data i.e. 1,2,5,10 is in A2 and B1 to K1 is numbered as 1 to 10 then

in b2 copy paste below
=SUMPRODUCT(--(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",100)),100*(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))),100))+0=B$1))
drag down and drag to left.

Hello Sioux ! Assuming your data i.e. 1,2,5,10 is in A2 and B1 to K1 is numbered as 1 to 10 then

in b2 copy paste below
=SUMPRODUCT(--(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",100)),100*(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))),100))+0=B$1))
drag down and drag to left.


if you don't want 0 to take place then use
=if(SUMPRODUCT(--(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",100)),100*(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))),100))+0=B$1))=0,"",SUMPRODUCT(--(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",100)),100*(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))),100))+0=B$1)))

Thank you, I think that works. Trying it now.

EDIT: That is excellent. Thank you, Hamesh. It's going to take me a while to work through your formula but it seems robust and works well above 10 as far as I can tell.

Hi,

Welcome to the forum

See the attached workbook for a macro....

Sub SplitData()
Dim rC As Range
Dim x As Long
Dim i As Long
Dim a As Long
Dim c As Long
    
    Application.ScreenUpdating = False
    c = Range("A" & Rows.Count).End(xlUp).Row
    For Each rC In Range("A2:A" & c).Cells
        x = Len(rC.Value) - Len(Replace(rC.Value, ",", ""))
        For i = 1 To x + 1
            a = Split(rC.Value, ",")(i - 1)
            Cells(rC.Row, a + 1).Value = 1
        Next i
    Next rC
    On Error Resume Next
    Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0
    Application.ScreenUpdating = True
End Sub

I think your cells are formatted as text. Try changing formatting to general or to number. then input the formula again

You are welcome Sioux and Thanks for the feedback.
Glad I could help

Out of interest, did you try the macro i posted.
The macro will not have any issues with the length of the number. It uses the commas to split the data per column.

No worries...just asking for interest sake
The great thing about forums is that you get various solutions and as the initiator, you can pick and choose from them at your will.

Cheers

Excel formulas are extremely powerful and flexible, esp. those in the array group, but sometimes macros can make things much easier; especially when you need multiple nests in array formulas.

NO it is not there in the instance number of substitute,
=SUMPRODUCT(--(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",100)),100*(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))),100))+0=B$1))
lets say in A2 you have 1,20,10
Red Part: Start of the mid
add a comma before the text then substitutes all commas with a string of 100 spaces. Now think for 1 comma 100 spaces means length is 100+1(length of 1) for second comma again 100 spaces means length at this moment is 101+100+2 = 203 for third comma it becomes 203+100+2 = 305.

Blue Part: starting number for mid
100*(ROW(INDIRECT("1:"&LEN($A2)-LEN(SUBSTITUTE($A2,",",""))+1))) will become {100;200;300}

last part of mid is 100 which is character to return.

for first instance mid is asking start at 100 position and take 100 characters at this junction first character will be at 101st position and mid is looking for character between 100th - 200th position

but for second instance mid is asking start at 200 position and take 100 characters at this junction first character will be at 203rd position and mid is looking for character between 200-300 position and so on for other instances

with higher number in rept you can check for much big string with in MID.

Trim then removes all excessive spaces and addition of 0 converts array of strings returned by MID to array Numbers
=B$1 check if any number within array matches with the B$1 if yes then gives true's and If no then gives false

-- mark converts trues to 1 and False's to 0 and then sumproduct sums all 1's and 0's.

as sumproduct can handle array operations you don't need to confirm the formula with array confirmation