View Single Post
  #2   Report Post  
Old March 20th 19, 10:15 PM posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,785
Default Pick up specific marked text (one or more then one) out of a cell and combine them into another

Hi Johan,

Am Wed, 20 Mar 2019 14:49:28 -0700 (PDT) schrieb :

In column A Cell 1 till the last, I've got combined text like;

[{"xxxx":356666,"Name":"AA1234"},{"yyyy":356667,"Na me":"BB3896"},{"yyyy":357633,"Name":"CC38901"}]

Whats the problem..... (and question).

All the values after name":" and thereafter between the quotes must be copied to the same row column B in a new Cell and separated with ";".

The above in A1 result in B1 then in;


The macro picks up Name":" and takes the text thereafter till the next " copied this to column B and then looks to the next same condition. All found text must be copieed to the same row column B and combining the results with a split ";"


Sub Test()
Dim varTmp As Variant, varData As Variant, varOut() As Variant
Dim LRow As Long, i As Long, j As Long
Dim n As Integer
Dim strTmp As String

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
varData = .Range("A1:A" & LRow)
For i = LBound(varData) To UBound(varData)
strTmp = ""
varTmp = Split(varData(i, 1), "Name"":""")
For n = 1 To UBound(varTmp)
strTmp = strTmp & ";" & Left(varTmp(n), InStr(varTmp(n), "}") - 1)
ReDim Preserve varOut(j)
varOut(j) = Replace(Mid(strTmp, 2), """", "")
j = j + 1
.Range("B1").Resize(UBound(varOut) + 1) = Application.Transpose(varOut)
End With
End Sub

Claus B.
Office 2016