Parsing

Anna112233

New Member
Joined
Nov 26, 2019
Messages
5
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
Platform
  1. Windows
Hello!
I have a column that has a lot of rows with different text, but the logic of it is always the same.
I need to parse this data to columns. Every element should be splitted after "," . And this {} shouldn't be included.
So the destination column name are like Key1, Number 1, Meaning 1, Key2, Number 2, Meaning 2 and so on.
Please, help me with this problem, because split function doesn't work here and I don't know how to parse into several columns.
This is the example:
{{Avaya,54366,}.{Tabel,443948,}}
{{Avaya,54476,}.{Tabel,443958,}.{DPA,Max@gmail,}}
{{DPA,Anna@gmail,}.{Avaya,56466,}.{Tabel,453948,}.{Chat,331212}}
{{Tabel,444948,}.{Avaya,54466,}}
{{Avaya,54466,}.{Tabel,443948,}.{DPA,Th@gmail,}}
{{Chat,121312}.{Avaya,54466,}.{Tabel,443948,}.{DPA,Dm@gmail,}}
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
replace { with nothing
replace } with nothing
replace . (dot) with nothing
then use text 2 columns by , (comma)
 
Upvote 0
So I replaced {, } and dot, but I don't know what to do next...

Sub SplitOnFirstColon()
Serie = 1

While Not Worksheets("Sheet1").Cells(Serie, 1) = ""
Worksheets("Sheet1").Columns("A").Replace _
What:="{", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True
Worksheets("Sheet1").Columns("A").Replace _
What:="}", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True
Worksheets("Sheet1").Columns("A").Replace _
What:=".", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True

'then use text 2 columns by , (comma)
Serie = Serie + 1


ActiveCell.Offset(1, 0).Activate
Wend
End Sub
 
Upvote 0
So I replaced {, } and dot, but I don't know what to do next...

Sub SplitOnFirstColon()
Serie = 1

While Not Worksheets("Sheet1").Cells(Serie, 1) = ""
Worksheets("Sheet1").Columns("A").Replace _
What:="{", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True
Worksheets("Sheet1").Columns("A").Replace _
What:="}", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True
Worksheets("Sheet1").Columns("A").Replace _
What:=".", Replacement:="", _
SearchOrder:=xlByColumns, MatchCase:=True

'then use text 2 columns by , (comma)
Serie = Serie + 1


ActiveCell.Offset(1, 0).Activate
Wend
End Sub
Hi this code do it same job
VBA Code:
Sub ddsad()
Dim cell As Range
For Each cell In Selection
ActiveCell.Value = Application.WorksheetFunction.Substitute( _
                    Application.WorksheetFunction.Substitute( _
                    Application.WorksheetFunction.Substitute(ActiveCell.Value, "{", ""), "}", ""), ".", "")
Cells(cell.Row, cell.Column).Offset(1, 0).Activate
Next cell
End Sub

And tell me, is this result that you want?
Book1
DEFGHIJKLMNO
20Key1 Number 1 Meaning 1 Key2 Number 2 Meaning 2Key3 Number 3 Meaning 3Key4 Number 4 Meaning 4
21Avaya54366Tabel443948
22Avaya54476Tabel443958DPAMax@gmail
23DPAAnna@gmailAvaya56466Tabel453948Chat331212
24Tabel444948Avaya54466
25Avaya54466Tabel443948DPATh@gmail
26Chat121312Avaya54466Tabel443948DPADm@gmail
Sheet15
 
Upvote 0
{{Chat,121312,}.{Avaya,54466,}.{Tabel,443948,}.{DPA,Dm@gmail,}}
I am assuming the missing comma (shown added in red above) was a typo on your part and not an indication that the second comma within each bracketed piece of text might not always be there. If so, see if this macro does what you want...
VBA Code:
Sub Anna112233()
  Dim R As Long, Data As Variant, Arr As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  Application.ScreenUpdating = False
  For R = 1 To UBound(Data)
    Arr = Split(Replace(Mid(Data(R, 1), 3, Len(Data(R, 1)) - 4), "}.{", ","), ",")
    Cells(R, "C").Resize(1, UBound(Arr) + 1) = Arr
  Next
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
but I don't know what to do next...
See if this is what you mean, processing the whole column together. (Same assumption about the missing comma as Rick).
Assumes data in column A

VBA Code:
Sub TTC()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    .Replace What:="}.{", Replacement:=",", LookAt:=xlPart
    .Replace What:="}}", Replacement:=""
    .Replace What:="{{", Replacement:=""
    .TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=False, Comma:=True, Other:=False
  End With
End Sub

Sample data & results

Book1
ABCDEFGHIJKL
1{{Avaya,54366,}.{Tabel,443948,}}Avaya54366Tabel443948
2{{Avaya,54476,}.{Tabel,443958,}.{DPA,Max@gmail,}}Avaya54476Tabel443958DPAMax@gmail
3{{DPA,Anna@gmail,}.{Avaya,56466,}.{Tabel,453948,}.{Chat,331212}}DPAAnna@gmailAvaya56466Tabel453948Chat331212
4{{Tabel,444948,}.{Avaya,54466,}}Tabel444948Avaya54466
5{{Avaya,54466,}.{Tabel,443948,}.{DPA,Th@gmail,}}Avaya54466Tabel443948DPATh@gmail
6{{Chat,121312,}.{Avaya,54466,}.{Tabel,443948,}.{DPA,Dm@gmail,}}Chat121312Avaya54466Tabel443948DPADm@gmail
TTC (5)
 
Upvote 0
If you want it with formulas, put the formula in B2 drag and down

Book1
ABCDEFGHIJKL
1TEXTRESULT
2{{Avaya,54366,}.{Tabel,443948,}}Avaya54366 Tabel443948      
3{{Avaya,54476,}.{Tabel,443958,}.{DPA,Max@gmail,}}Avaya54476 Tabel443958 DPAMax@gmail   
4{{DPA,Anna@gmail,}.{Avaya,56466,}.{Tabel,453948,}.{Chat,331212,}}DPAAnna@gmail Avaya56466 Tabel453948 Chat331212
5{{Tabel,444948,}.{Avaya,54466,}}Tabel444948 Avaya54466      
6{{Avaya,54466,}.{Tabel,443948,}.{DPA,Th@gmail,}}Avaya54466 Tabel443948 DPATh@gmail   
7{{Chat,121312,}.{Avaya,54466,}.{Tabel,443948,}.{DPA,Dm@gmail,}}Chat121312 Avaya54466 Tabel443948 DPADm@gmail
Sheet
Cell Formulas
RangeFormula
B2:L7B2=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"}.{",","),"{",""),"}",""),",",REPT(" ",99)),((COLUMNS($B$1:B1)-1)*99)+1,99))


------------------------------------------------------------------------
Or you can use the following formula in B2 copy down and then use the excel function for Text to Columns.

Book1
AB
1TEXTRESULT
2{{Avaya,54366,}.{Tabel,443948,}}Avaya,54366,,Tabel,443948,
3{{Avaya,54476,}.{Tabel,443958,}.{DPA,Max@gmail,}}Avaya,54476,,Tabel,443958,,DPA,Max@gmail,
4{{DPA,Anna@gmail,}.{Avaya,56466,}.{Tabel,453948,}.{Chat,331212,}}DPA,Anna@gmail,,Avaya,56466,,Tabel,453948,,Chat,331212,
5{{Tabel,444948,}.{Avaya,54466,}}Tabel,444948,,Avaya,54466,
6{{Avaya,54466,}.{Tabel,443948,}.{DPA,Th@gmail,}}Avaya,54466,,Tabel,443948,,DPA,Th@gmail,
7{{Chat,121312,}.{Avaya,54466,}.{Tabel,443948,}.{DPA,Dm@gmail,}}Chat,121312,,Avaya,54466,,Tabel,443948,,DPA,Dm@gmail,
Sheet
Cell Formulas
RangeFormula
B2:B7B2=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"}.{",","),"{",""),"}","")
 
Upvote 0

Forum statistics

Threads
1,224,814
Messages
6,181,123
Members
453,021
Latest member
Justyna P

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