Parse data via vba and get the output in right column

L

lady_alina

Board Regular
Joined
Feb 18, 2015
Messages
52
Hi,

I hope I can get help from the gurus here at the forum.

Here is the problem I have data in one column as shown below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name:AAA,Age:25,Sex:F,Location:KKK,Ref#:001[/TD]
[/TR]
[TR]
[TD]Name:BBB,Age:26,type:2,Sex:F,Location:KKK,Ref#:001[/TD]
[/TR]
[TR]
[TD][TABLE="width: 406"]
<tbody>[TR]
[TD="class: xl65, width: 406"]Name:BBB,Age:29,type:2,Sex:F,Course:DDD,Location:KKK,Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I want to separate the data based on comma however I only need Name,Age,Sex,Location. But when I use text to column option>deliminated>comma it gives gives me data in wrong column as shown below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Name:AAA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="width: 47"]Age:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="width: 44"]Sex:F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Location:KKK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Name:BBB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="width: 47"]Age:26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="width: 44"]type:2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Sex:F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Location:KKK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Name:BBB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="width: 47"]Age:29[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="width: 44"]type:2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Sex:F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Course:DDD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]Location:KKK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I want all location in one column, ref# in one column. Can anyone help me with this please.

Thanks in advance.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Try this:-
Data assumed to start in "A1"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG29Jul02
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Tcc [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
s = ""
Sp = Split(Dn.Value, ",")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Split(Sp(n), ":")(0)
            [COLOR="Navy"]Case[/COLOR] "Name": s = s & "," & Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Age": s = s & "," & Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Sex": s = s & "," & Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Location": s = s & "," & Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Ref#": s = s & "," & Sp(n)
        [COLOR="Navy"]End[/COLOR] Select
    [COLOR="Navy"]Next[/COLOR] n
Tcc = Split(Mid(s, 2), ",")
[COLOR="Navy"]With[/COLOR] Dn.Resize(, UBound(Tcc) + 1)
    .Value = Tcc
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks for the quick reply Mick. I tried the code but it doesn't give me desired output.

[TABLE="width: 406"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Name:AAA,Age:25,Sex:F,Location:KKK,Ref#:001[/TD]
[/TR]
[TR]
[TD]Name:BBB,Age:25,type:2,Sex:F,Location:KKK,Ref#:001[/TD]
[/TR]
[TR]
[TD]Name:BBB,Age:25,type:2,Course:DDD,Location:KKK,Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]

If you see in the next row third row it doesn't have gender so in place of Sex it gives me Location. Is there any way of avoiding that if any of the required field is missing.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Name:AAA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl65, width: 47"]Age:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]Sex:F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Location:KKK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Name:BBB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl65, width: 47"]Age:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]Sex:F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Location:KKK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl65, width: 70"]Name:BBB[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 47"]
<tbody>[TR]
[TD="class: xl65, width: 47"]Age:25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]Location:KKK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Ref#:001[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thank you again
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG30Jul09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Sp [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
Sp = Split(Dn.Value, ",")
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Sp)
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Split(Sp(n), ":")(0)
            [COLOR="Navy"]Case[/COLOR] "Name": Dn.Value = Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Age": Dn.Offset(, 1).Value = Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Sex": Dn.Offset(, 2).Value = Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Location": Dn.Offset(, 3).Value = Sp(n)
            [COLOR="Navy"]Case[/COLOR] "Ref#": Dn.Offset(, 4).Value = Sp(n)
        [COLOR="Navy"]End[/COLOR] Select
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA to use a weighting and rescaling table against information in a separate sheet
Replies
3
Views
233
StephenCrump
S
M
  • Question Question
Customer Ageing Different Formulas
Replies
3
Views
227
Alex Blakenburg
A
L
  • Solved
Excel Formula Multiple if then
2
Replies
12
Views
558
Lisa P NYC
L
S
  • Question Question
Copying data into next available column to the right using macros?
Replies
3
Views
229
RobP
R
M
  • Question Question
VBA - how to fill blanks with the same information in the same column but referencing to another column
Replies
5
Views
410
mumps
mumps

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top