split values into multple cells based on criteria

[kanth_sri_c]

I've a cell with concatenated values separated by commas like this - [TABLE="width: 243"]
<TBODY>[TR]
[TD]10,20,AA,BB,30,40, CC, DD</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

I need them to split into 2 cells based on criteria
If 10,AA,CC show it in a new cell as -
[TABLE="width: 243"]
<TBODY>[TR]
[TD]10, AA,CC
And if the rest (20,BB,30,BB, DD) then in another cell.

Any help appreciated! thanks
</SPAN>
[/TD]
[/TR]
[TR]
[TD]</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[MickG]

Data in "A1" Results "B1" and "C1"

[COLOR="Navy"]Sub[/COLOR] MG09Aug23
[COLOR="Navy"]Dim[/COLOR] Txt
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Txt = Split([a1], ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Txt)
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Trim(Txt(n))
        [COLOR="Navy"]Case[/COLOR] "10", "AA", "CC": [b1] = [b1] & "," & Trim(Txt(n))
        [COLOR="Navy"]Case[/COLOR] Else: [C1] = [C1] & "," & Trim(Txt(n))
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]Next[/COLOR] n
[b1] = Mid([b1], 2)
[C1] = Mid([C1], 2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kanth_sri_c]

thanks Mick..

It throws an compile error: end of statement missing

 

[MickG]

Have you copied it correctly ??, I've just copied it back and it works for me.

 

[kanth_sri_c]

I changed MG09Aug23 to my worksheet name
and ran the macro to get the error message

 

[kanth_sri_c]

MikeG, The macro worked for me. Thanks for your quick response and solution

 

[MickG]

You welcome
Regrds Mick

 

[kanth_sri_c]

Mick, My list with concatenated values is growing. How i apply a for loop so that i can have the macro run for A2, A3 etc
just like the one above worked for A1 (10,20,AA,BB,30,40, CC, DD)

 

[MickG]

Try this for Data in column "A".

[COLOR="Navy"]Sub[/COLOR] MG10Aug27
[COLOR="Navy"]Dim[/COLOR] Txt
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Txt = Split(Dn, ",")
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Txt)
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Trim(Txt(n))
        [COLOR="Navy"]Case[/COLOR] "10", "AA", "CC": Dn.Offset(, 1) = Dn.Offset(, 1) & "," & Trim(Txt(n))
        [COLOR="Navy"]Case[/COLOR] Else: Dn.Offset(, 2) = Dn.Offset(, 2) & "," & Trim(Txt(n))
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]Next[/COLOR] n
Dn.Offset(, 1) = Mid(Dn.Offset(, 1), 2)
Dn.Offset(, 2) = Mid(Dn.Offset(, 2), 2)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[kanth_sri_c]

it worked - thanks again