Thread: Separate numbers from characters that are separated by comma

Hi there, I am a newbie here with some time spent in front of computer ... crazy way to say it ..

Anyway, I have this big data set that needs a bit of work and I would like some help with that. For example the cell contains 1,M2,M7,M1,M8:2,M15,M9,M4,M5:3,M3,M6,M14,M11,M12:4,M10,M13 and I need to create M1, M2 , M3 ..etc columns and attach the coresponding number to each one of them. For instance M2,M7,M1,M8will get 1 and so forth and so on. I was acutally thinking using the Notepad to replace the , with a TAB space and paste them back in to my data set and create some IF statements. On the other hand running VBA scrip would make it even easier. However I would preffer to use a function (that I am not 100% familiar with) and leave the VBA scrip out of the question for the moment.

Any help would be greatly appreciated.

If you select the column and go to DATA|Text to Columns... select Delimited, click Next, Select Comma, click Finish.. that should separate for you in one shot.

If you're looking to accomplish something more finessed, then post a sample workbook showing a RAW DATA page, then a mockup manually your desired results from that sample data, maybe we can provide more assistance.

Originally Posted by JBeaucaire

If you're looking to accomplish something more finessed, then post a sample workbook showing a RAW DATA page, then a mockup manually your desired results from that sample data, maybe we can provide more assistance.

If you have 3 or four rows of raw data that can fully represent your desired output, great. If not, use 6-8, however many are needed until a full representation of the complete output can be represented.

Then do the mockup exactly like you want it. Do this in a workbook since this is an Excel forum.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Try this:

Code:

Option Explicit

Sub ParseData()
'JBeaucaire   2/17/2010
'Reformat 2-column data into a specific table format
Dim LR As Long, i As Long, v As Long
Dim MyArr As Variant, MyVal As Long, ws As Worksheet
Application.ScreenUpdating = False
On Error Resume Next

'Create report sheet if needed
    If Not Evaluate("=ISREF(Report!A1)") Then _
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Report"
    
Set ws = Sheets("Report")       'Sheet to create new data format
ws.Cells.Clear                  'clear old data if any
                                'add titles
ws.Range("A1:P1") = [{"ID","M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12","M13","M14","M15"}]

With Sheets("Data")                                     'the sheet with the raw data
    LR = .Range("A" & .Rows.Count).End(xlUp).Row        'bottom row of data
    For i = 2 To LR                                     'loop one row at a time
        ws.Cells(i, "A") = .Cells(i, "A")               'copy column A code over
        MyArr = Split(Replace(.Cells(i, "B"), ":", ","), ",")   'split column B into separate values
        For v = LBound(MyArr) To UBound(MyArr)
            Select Case MyArr(v)                        'test each value
                Case 1 To 99 ^ 99                       'if numeric, memorize
                    MyVal = MyArr(v)
                Case vbNullString                       'if blank, ignore
                    'nothing
                Case Else                               'if text, find column and insert memorized numeric value
                    ws.Cells(i, Replace(MyArr(v), "M", "") + 1) = MyVal
            End Select
        Next v
    Next i
End With

Application.ScreenUpdating = True
End Sub

Name the sheet with the raw data on it Data. The macro should do the rest.

This is the programming forum, so solutions offered are typically expected to be VBA-based. Did you post in the wrong forum?
Based on your workbook sample, I wouldn't think you'd be any happier with the array-type formulas that might be able to get you what you want.

Meanwhile, the macro I've given you is pretty much ready to use. You should try it out.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Name the sheet with the raw data on it Data
7. Save your workbook as a macro-capable workbook (*.xlsm)

The macro is installed and ready to use.

Press Alt-F8 and select ParseData from the macro list. It will create the Report sheet for you.

Originally Posted by hawk77EF

I was thinking more on the lines of using : if((and(isnumber(search("1",Work2!B2),"1",IF(isnumber(search("M1",Work2!B2)") but I couldn't finish.

Something like
If Work2!B2 has 1 in it and also M1 and the header is M1 then the value for that cell is one
if work2!B2 has 2 in it and also M1 and the header is M1 then the value is 2

That method would be pretty problematic since there is a 1 in M1, M11 and a 2 in M2 and M12....etc.
There's also an M1 in M11...you see the problem.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

I could look silly coming up with something that is very advanced.

I think that depends on if you try to pass it off as something you did alone. "I have some Excel friends who helped me put together a macro to do this quickly for us..."

...that keeps you from looking like anything other than resourceful, doesn't it? I used forum assistance quite extensively over the years, and no one ever thought twice about something when it worked.