Parsing

[Anna112233]

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,}}

 

[sandy666]

replace { with nothing
replace } with nothing
replace . (dot) with nothing
then use text 2 columns by , (comma)

 

[Anna112233]

replace { with nothing
replace } with nothing
replace . (dot) with nothing
then use text 2 columns by , (comma)

It doesn't look like a VBA code...

 

[Anna112233]

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

 

[sandy666]

It doesn't look like a VBA code...

of course not
use built-in features

 

[hernantorres23]

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

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
	D	E	F	G	H	I	J	K	L	M	N	O
20	Key1	Number 1	Meaning 1	Key2	Number 2	Meaning 2	Key3	Number 3	Meaning 3	Key4	Number 4	Meaning 4
21	Avaya	54366		Tabel	443948
22	Avaya	54476		Tabel	443958		DPA	Max@gmail
23	DPA	Anna@gmail		Avaya	56466		Tabel	453948		Chat	331212
24	Tabel	444948		Avaya	54466
25	Avaya	54466		Tabel	443948		DPA	Th@gmail
26	Chat	121312		Avaya	54466		Tabel	443948		DPA	Dm@gmail
Sheet15

 

[Rick Rothstein]

{{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...

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

 

[Peter_SSs]

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

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
	A	B	C	D	E	F	G	H	I	J	K	L
1	{{Avaya,54366,}.{Tabel,443948,}}	Avaya	54366		Tabel	443948
2	{{Avaya,54476,}.{Tabel,443958,}.{DPA,Max@gmail,}}	Avaya	54476		Tabel	443958		DPA	Max@gmail
3	{{DPA,Anna@gmail,}.{Avaya,56466,}.{Tabel,453948,}.{Chat,331212}}	DPA	Anna@gmail		Avaya	56466		Tabel	453948		Chat	331212
4	{{Tabel,444948,}.{Avaya,54466,}}	Tabel	444948		Avaya	54466
5	{{Avaya,54466,}.{Tabel,443948,}.{DPA,Th@gmail,}}	Avaya	54466		Tabel	443948		DPA	Th@gmail
6	{{Chat,121312,}.{Avaya,54466,}.{Tabel,443948,}.{DPA,Dm@gmail,}}	Chat	121312		Avaya	54466		Tabel	443948		DPA	Dm@gmail
TTC (5)

 

[DanteAmor]

If you want it with formulas, put the formula in B2 drag and down

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	TEXT	RESULT
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
Range	Formula
B2:L7	B2	=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
	A	B
1	TEXT	RESULT
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
Range	Formula
B2:B7	B2	=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"}.{",","),"{",""),"}","")