Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick up specific marked text (one or more then one) out of a cell andcombine them into another
Dear,
As part of my earlier request (with thanks to Claus !), I have another small issue to solve. So again...... if somebody can help me out :) 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; AA1234;BB3896;CC38901 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 ";" This looks a difficult one ? I hope for a solution. regards, Johan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick up specific marked text (one or more then one) out of a cell and combine them into another
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 23:15:06 +0100 schrieb Claus Busch: Sub Test() Dim varTmp As Variant, varData As Variant, varOut() As Variant you can also do it with Regular Expressions: Sub Test2() Dim varData As Variant, varOut() As Variant Dim LRow As Long, i As Long Dim n As Integer Dim strTmp As String Dim re, ptrn, Match, Matches ptrn = "[A-Z]{1,2}[0-9]{1,6}" Set re = CreateObject("vbscript.regexp") re.Pattern = ptrn re.IgnoreCase = False re.Global = True With ActiveSheet LRow = .Cells(.Rows.Count, 1).End(xlUp).Row varData = .Range("A1:A" & LRow) For i = LBound(varData) To UBound(varData) strTmp = "" Set Matches = re.Execute(varData(i, 1)) For Each Match In Matches strTmp = strTmp & ";" & Match Next ReDim Preserve varOut(UBound(varData) - 1) varOut(i - 1) = Mid(strTmp, 2) Next .Range("B1").Resize(UBound(varOut) + 1) = Application.Transpose(varOut) End With End Sub Regards Claus B. -- Windows10 Office 2016 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pick up specific marked text (one or more then one) out of a celland combine them into another
THANKS VERY VERY MUCH !!
The first one works oke. Regards, Johan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pick a specific cell within a macro .... ?? | Excel Discussion (Misc queries) | |||
Pick up some text in a cell | Excel Worksheet Functions | |||
Marking cell; totaling all # between marked cell & next marked cel | Excel Worksheet Functions | |||
pick from 'drop down list' OR enter text in cell | Excel Worksheet Functions | |||
Create a pick list to use to go to a text cell in Excel | Excel Worksheet Functions |